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-

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-
>
>
>

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-" <[email protected]> wrote in message
news:[email protected]...
> 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-
>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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-" <[email protected]> wrote in message
> news:[email protected]...
>> 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-
>>
>>
>>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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-" <[email protected]> wrote in message
> news:[email protected]...
> > 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-
> >
> >
> >
>
>

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-" <[email protected]> wrote in message
> news:[email protected]...
> > 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-
> >
> >
> >
>
>
>

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-
> >
> >
> >

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-" <[email protected]> wrote in message
news:[email protected]...
>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-" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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-
>> >
>> >
>> >
>>
>>
>>