Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
While trying to make changes to a large number of option buttons on a sheet,
I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
Unfortunately, this will not work because VBA sees the variable MyOB as a
string variable and not as an object variable. If you are using 200 option buttons you have to write out 200 statements to change the values. Alok "-JEFF-" wrote: While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
Hi JEFF,
Problem is you're trying to use the name of the optionbutton (a string) as if it were the object (optionbutton) itself. Also, the functions CStr and Trim aren't necessary here. Try this: Sub MySub() Dim i As Long For i = 1 To 200 OptionButtons("OptionButton" & i).Value = False Next i End Sub Regards, KL "-JEFF-" wrote in message ... While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
Hi again,
Just to say that my code assumes that you are using the optionbutton control from the Forms toolbar. If you are using the activex controls from Visual Basic toolbar, then you could use this code: Sub MySub() Dim i As Long For i = 1 To 4 OLEObjects("OptionButton" & i).Object.Value = False Next i End Sub Regards, KL "KL" wrote in message ... Hi JEFF, Problem is you're trying to use the name of the optionbutton (a string) as if it were the object (optionbutton) itself. Also, the functions CStr and Trim aren't necessary here. Try this: Sub MySub() Dim i As Long For i = 1 To 200 OptionButtons("OptionButton" & i).Value = False Next i End Sub Regards, KL "-JEFF-" wrote in message ... While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
and it they are option buttons from the control toolbox toolbar as opposed
to the forms toolbar (as illustrated by KL), you would use Sub MySub() Dim i As Long For i = 1 To 200 Activesheet.OleObjects("OptionButton" & i).Object.Value = False Next i End Sub -- Regards, Tom Ogilvy "KL" wrote in message ... Hi JEFF, Problem is you're trying to use the name of the optionbutton (a string) as if it were the object (optionbutton) itself. Also, the functions CStr and Trim aren't necessary here. Try this: Sub MySub() Dim i As Long For i = 1 To 200 OptionButtons("OptionButton" & i).Value = False Next i End Sub Regards, KL "-JEFF-" wrote in message ... While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
I am using Excel 2000 9.0.6926 SP-3. When I run your code I get an error sub
or function not defined [OptinButtons( )]. Any ideas? -JEFF- "KL" wrote: Hi JEFF, Problem is you're trying to use the name of the optionbutton (a string) as if it were the object (optionbutton) itself. Also, the functions CStr and Trim aren't necessary here. Try this: Sub MySub() Dim i As Long For i = 1 To 200 OptionButtons("OptionButton" & i).Value = False Next i End Sub Regards, KL "-JEFF-" wrote in message ... While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
I actually have several thousand. I have 16 option buttons on each row set
up into three groups. Do you know if I can use a For Each loop? -JEFF- "Alok" wrote: Unfortunately, this will not work because VBA sees the variable MyOB as a string variable and not as an object variable. If you are using 200 option buttons you have to write out 200 statements to change the values. Alok "-JEFF-" wrote: While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable as name of object does not work - Why?
Hi Jeff,
You are most probably using the ActiveX controls from the Visual Basic toolbar, so see the posts from Tom and myself (the second one) above. Regards, KL "-JEFF-" wrote in message ... I am using Excel 2000 9.0.6926 SP-3. When I run your code I get an error sub or function not defined [OptinButtons( )]. Any ideas? -JEFF- "KL" wrote: Hi JEFF, Problem is you're trying to use the name of the optionbutton (a string) as if it were the object (optionbutton) itself. Also, the functions CStr and Trim aren't necessary here. Try this: Sub MySub() Dim i As Long For i = 1 To 200 OptionButtons("OptionButton" & i).Value = False Next i End Sub Regards, KL "-JEFF-" wrote in message ... While trying to make changes to a large number of option buttons on a sheet, I want to increment a number and concactinate it in order to change the correct optionbutton. But this does not work: Sub MySub dim i i = 1 for i = 1 to 200 MyOB = "OptionButton" & trim(str(i)) MyOB.value = False 'or any of the other properties I need to change next i end It does work when I use the actual optionbutton name instead of the variable, i.e. OptionButton2.value = False -JEFF- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |