I have a userform that has a lot of textboxes that are formatted as date fields. Some of them have a default value and are locked=true, enabled=false. For those ones I have a checkbox next to them so the user can unlock and enable the textbox if they so desired.
Well the checkboxes and textboxes have a similar naming scheme, but are not numeric (they are not CheckBox1, CheckBox2, etc). As an example they one grouping is Cust1RelExpUnlock (this is the checkbox) and Cust1RelExpDateBox (this is the textbox). The difference in their name is the last portion (Unlock or DateBox).
Now when the checkbox is selected the code I want looks something like this:
Code: Private Sub Cust1RelExpUnlock_Click()UnlockDateBox Cust1RelExpUnlockEnd Sub
Where UnlockDateBox is the function I'm having difficulty with and is supposed to be generic enough to work on any grouping assuming my naming scheme is consistent.
Using a combination of the Left() and Len() functions I can get the unique aspects of the name. Then concatenate it with "DateBox" as required. However I'm getting type mismatch errors, or object required erros. I tried various combinations of Dim _____ As Object, As Control, As Textbox... with no success.
I'm getting the correct name as a string, how do I make it work so I can reference the textbox.enabled/.locked?
Code: Private Sub UnlockDateBox(Ck) Dim CkDate As Control 'Tried control, textbox, object Set CkDate = ProjectInputForm.MultiPage1.Object ' tried just using ProjectInputForm, and Object Dim CkName As String
Sub test() Dim rng, dest, c As Range Dim result As String Worksheets("sheet1").Activate Set rng = Range([a1], [a1].End(xlDown)) Set dest = Range("d1") result = "" For Each c In rng result = result & " " & c Next MsgBox result dest = result End Sub
the penultimate code statement "dest=result" does notwork the value of "result" is not entered in "dest" though no error comes up if I use "dest.value=result" it gives error "object required" If I use "range("d1")=result" it works.what is the theoretical mistake.
I would like to have a UDF to convert any text string to an abbreviation. For example, "Del Puerto Creek at HWY" would be converted to DPCAH. Or "this text string" would be converted to TTS. This has to work on any string of any length in any cell. I guess the UDF would pass each character through a loop and extract the letter immediately after a space and capitalize the letter if it is not already in uppercase. The UDF would have to have the ability to drag using relative references. For example: =AbbrevTxt(A1). It does not matter what the UDF is named.
I'm working in Excel 2003. Is there a way to convert strings to Ranges on the Excel commandline?
I have a lookup function in an estimate sheet that uses 2 ranges, stored as strings, in another worksheet. They are used to return a price from another workbook. When I use named ranges to access them with the lookup function, it doesn't work. But when I cut and paste the strings into the commandline it works prefectly?
$A10 = Part Number to search for
MaterialOptions is a named Range eg: '[Catalog.xls]PriceList'!$A$13:$A$17
PriceOptions is a named Range eg: '[Catalog.xls]PriceList'!$Z$13:$Z$17
I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.
My database has 6 fields names and I have created the following code to capture and post data to the database form a userform. It works but I am sure there is a more efficient and elegant way to do this. The two areas I would like advice are: 1. converting the date string to a date. 2. the line of code where I subtract line6 from line4.
The following function reads many worksheets in one workbook and put the information into one worksheet. F22 to Q22 is dates in the format of mmm-yy on the many worksheets. On the one worksheet B1, C1, D1, etc... is dates also formatted mmm-yy. Now the many sheets only have a 12 month period but could be any date within 60 months. In the many worksheets the beginning date in F22 is based on the date that is entered into G8 of the many worksheets. The function is:
Code: Function HrsByMonth(strField As String, MonthNum As Integer) As Long Dim sht As Worksheet, i As Long, j As Integer HrsByMonth = 0
I've got an odd program that exports time data formatted as a text string. For example....
0:10:25
Since it has no numerical value, then I can't simply "format as number". I'm thinking that I will need to use one of the string methods eg: mid() to check each character individually and then convert that into the equivalent numerical value.
So the numerical output I am looking for in the above example would be 625.
I will post my code and point out where the error occurs.
Sub GetProd() 'Averages Daily Production over the month
Dim WellRange, MonthRange As Range Dim Month_ As Integer TotWells = Sheets("R").Cells(1, 2) TotMonths = Sheets("R").Cells(1, 4) Month_ = Sheets("R").Cells(2, 4)
For Flag = 1 To TotWells Set WellRange = DefineWellRange(Flag) WellRange.Select <-----Used this To check If Range gets passed back properly. For c = 1 To TotMonths Set MonthRange = DefineMonthRange(Month_, (WellRange)) <---Error Here Next c Next Flag
End Sub ...
So the error occurs in the main "GetProd" Sub at the point when it calls the DefineMonthRange Function, its a 424 "Object Required" Error. Just before this is called I have a "WellRange.Select" which I was using to make sure that WellRange is in fact a range, and it does select the appropriate area.
So My question is of course, why I get an object error even though I am passing a range to a function which is expecting a range?
How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example: .....
I got a list of counties in Texas, but the list is in mixed case and always has a " County" after each one. I got it converting to upper case, and have tried removing the " County" but no luck. Below is the code I have:
Code: Sub TEXAS() For a = 1 To 254 R = "D" & a i = "a" & a ActiveSheet.Range(R) = UCase(ActiveSheet.Range(i)) Next a End Sub
I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.
Code: Sub ConvertTextToBritStyleDate() ' dd/mm/yyyy (not mm/dd/yyyy) ' Select the cells containing the dates (not the header)
I'm creating a custom function to cut down on redundant code. However, in converting the code to a function so that it may be used in different instances, I'm getting an unexpected error when the code executes.
The error is:
Code: Object doesn't support this property or method.
Here is my function:
Code: Public Function chkClick(checkboxName As String, tabName As String, chartName As String, seriesNumber As Long) Sheets(tabName).ChartObjects(chartName).Activate If Sheets(tabName).checkboxName.Value = True Then ActiveChart.FullSeriesCollection(seriesNumber).Select
[code]....
The function checks the status of the checkbox name passed to it. If it's checked, it will show a chart series. If it's not checked, it will hide the chart series. The error comes in referencing the "checkboxName".
How might I need to alter the code so that I can use the form control name as a variable? This is ultimately one of the key components of the function as each control has it's own name.
If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then ..do code (I know the cell location of interest at this point) End If
Then I End up needing To Do something Like the following ActiveChart.SeriesCollection(1).XValues = _ Worksheets("RawData").Range("L1:N1")
�L1:N1� above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn�t do me much good when range wants something like �L1�. I don�t know how to convert that (12,16) to a L16 for example.
Is it possbile with the randbetween function to only randomate a number when a certin object is pressed? As at the moment, every button i press or drop down data i select it will randomate a number, which i dont want to happen.
On sheet 1 I have a list of 1000 firstnames On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
I don't have a clue as to how to convert this to VBA code.
When installed as a macro, the function works fine in a cell on my PC, but when I send the file to another pc, it still gives the name of my pc. I need it to return the name of thier pc.