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-

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-

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-

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-

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-

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-

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-

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-