Convert String To Object Or Function
Apr 11, 2007
I do not know if this is possible in VBA, but I am hoping it is as it will make my life much easier.
Is it possible to say have a TextBox and a CommandButton, that does the following?
Public Sub Execute()
Dim ObjectName As String
Dim Object As Object
ObjectName = txtObject.Text
Set Object = ObjectName
Object.Execute
End Sub
Or to take it a step further:
Public Sub Execute()
Dim ObjectName As String
Dim FunctionName As String
Dim Object As Object
ObjectName = txtObject.Text
FunctionName = txtFunction.Text
Set Object = ObjectName
Object.FunctionName
End Sub
Are there any inbuilt functions that will create/reference an object based on a string containing the name of the object or function?
View 9 Replies
ADVERTISEMENT
Apr 23, 2009
Convert object values to cell values!-requirement
i need to convert the values present in the drawing objects like "Rectangle" to cell value.
Eg:
A rectangle say "Rectangle 150" is having a value as "Customer name". Now i need to get this value in normal cell in the worksheet.
View 12 Replies
View Related
Feb 28, 2013
Here is the scoop.
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
[Code] .........
View 4 Replies
View Related
Mar 30, 2007
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.
View 2 Replies
View Related
Mar 18, 2013
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.
View 3 Replies
View Related
Aug 2, 2013
I have a field in the format of 121031. I need a formula to convert this field to a date DD/MM/YYYY format, so 121031 becomes 31/10/2012.
View 9 Replies
View Related
Mar 27, 2009
I have a string variable (myCell) that contains a cell address (i.e. $A$66)
I have a Range variable (mrR1) that I want to give that value to.
I cannot figure out how to do it. Can anyone help?
After that, I'm hoping to use the Range variables in a Goal Seek.
Private Sub CommandButton1_Click()
Dim myMatch As Double
Dim myCell As String
Dim myStart As Range
Dim myIRR As String
Dim myR1 As Range
Dim myR2 As Range
myMatch = WorksheetFunction.Match(0.25, Range("K67:DZ67"))
myCell = Range("HurdleStart").Offset(0, myMatch).Address
myIRR = Range("IRRStart").Offset(0, myMatch).Address
' myR1 = myIRR value
' myR2 = myCell value
Range(myR1).GoalSeek Goal:=0.25, ChangingCell:=Range(myR2)
End Sub
View 9 Replies
View Related
Apr 1, 2009
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
LOOKUP($A10,MaterialOptions,PriceOptions) = #VALUE!
But...
LOOKUP($A10,'[Catalog.xls]PriceList'!$A$13:$A$17,'[Catalog.xls]PriceList'!$Z$13:$Z$17) = Success!
View 9 Replies
View Related
Aug 6, 2013
This was my attempt, it says 'Object required'
Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, cancel As Boolean)
If Target.Column = 16 Then
Select Case Mid(Target.Text, 1, Worksheet.Function.Find("to", Target.Text, 1) - 2)
Case "CAS"
MsgBox ("Cashiers")
View 2 Replies
View Related
Mar 12, 2014
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.
Example
048051_14110/09/2013
048051_14111/09/2013
048051_14112/09/2013
048051_14113/09/2013
041929_14115/10/2013
041929_14116/10/2013
041929_14117/10/2013
041929_14118/10/2013
I want this to read
048051_141from - 10/09/2013 - to - 13/09/2013
041929_141from - 15/10/2013 - to -18/10/2013
View 1 Replies
View Related
Aug 23, 2009
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.
View 3 Replies
View Related
May 3, 2013
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
[Code].....
View 6 Replies
View Related
Feb 16, 2014
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.
View 2 Replies
View Related
Dec 18, 2009
I am trying to convert a number string date that has been stored in a database to a readable date time in an excel pivot table.
1260983366 needs to look like Wednesday, December 16, 2009, 10:09 AM.
The converted date could also look like Wed, Dec 16, 09, 10:09 AM.
View 9 Replies
View Related
Sep 28, 2007
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?
View 4 Replies
View Related
May 18, 2007
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: .....
View 9 Replies
View Related
Nov 7, 2008
Say I have a string "09800EBHR052708"
How would I take the first 5 characters and get a number out of them?
View 14 Replies
View Related
Mar 7, 2013
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
View 7 Replies
View Related
Aug 13, 2013
I have a date range that looks like this (British date style, not US):
edit: I posted an image but it can't be seen for some reason:
Date Range
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
Thus the first cell show 11 October 2010 to 17 October 2010 I want to return just the first day (the week commencing date):
edit: I posted an image but it can't be seen for some reason:
Week Commencing
11/10/2010
11/10/2010
11/10/2010
11/10/2010
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)
[Code]...
View 3 Replies
View Related
Mar 30, 2014
The following macro works:
Code:
Range("b42").Resize(, Len(Range("A42").Value)) = split(StrConv(Range("A42").Value, vbUnicode), Chr(0))
I tried to revise the macro so that instead of 'b42' it's activecell.
Code:
Dim b As Range
Set b = ActiveCell
Range(b).Resize(, Len(Range(b).Value)) = split(StrConv(Range(b).Value, vbUnicode), Chr(0))
My code isn't working.
View 2 Replies
View Related
Jun 18, 2014
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.
View 9 Replies
View Related
Jan 10, 2013
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
View 5 Replies
View Related
Sep 28, 2012
Is there a way to convert the string "Sep 28 2012" to excel date format using a formula or vba?
View 8 Replies
View Related
Nov 17, 2009
Convert the first letter of a string to a capital without altering the rest of the strings format.
example: the activecell contains the string a1st (lowercase a, superscript 1st)
how can i achieve this in VBA?
Additionally, is it also possible to convert all of a string to capitals except the superscript characters
View 9 Replies
View Related
May 18, 2007
I have something like the following:
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.
View 7 Replies
View Related
Mar 25, 2009
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.
View 2 Replies
View Related
Jan 10, 2013
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
View 2 Replies
View Related
Oct 13, 2009
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.
View 5 Replies
View Related
Dec 7, 2007
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.
View 14 Replies
View Related
Dec 7, 2009
Sub xxxxxxx()
Keli = Cells(13, 8).Value
Valid1 = Application.WorksheetFunction.VLookup(Day(Keli), Range("ToTalList"), 2, False)
Valid2 = Application.WorksheetFunction.VLookup(Month(Keli), Range("ToTalList"), 3, False)
Valid3 = Application.WorksheetFunction.VLookup(Int(Year(Keli) / 100), Range("ToTalList"), 4, False)
Valid4 = Application.WorksheetFunction.VLookup(Year(Keli) - Int(Year(Keli) / 100) * 100, Range("ToTalList"), 5, False)
MsgBox Valid1 & " " & Valid2 & " " & Valid3 & " " & Valid4
End Sub
When I try to convert it to a usefull function I get a #Name? Error
What do i do wrong? The purpose is to create a date in to words function
Public Function DateInWords(Keli As Range)
Valid1 = Application.WorksheetFunction.VLookup(Day(Keli.Value), ThisWorkbook.Worksheets(1).Range("ToTalList"), 2, False)
Valid2 = Application.WorksheetFunction.VLookup(Month(Keli.Value), ThisWorkbook.Worksheets(1).Range("ToTalList"), 3, False)
Valid3 = Application.WorksheetFunction.VLookup(Int(Year(Keli.Value) / 100), ThisWorkbook.Worksheets(1).Range("ToTalList"), 4, False)
Valid4 = Application.WorksheetFunction.VLookup(Year(Keli.Value) - Int(Year(Keli.Value) / 100) * 100, ThisWorkbook.Worksheets(1).Range("ToTalList"), 5, False)
DateInWords = Valid1 & " " & Valid2 & " " & Valid3 & " " & Valid4
End Function
View 9 Replies
View Related