Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Question about objects and OptionButtons

  Asked By: Pedro    Date: Jan 24    Category: MS Office    Views: 2942
  

I'm just learning VBA in Excel and have been going through the books,
but I can't find the answer to this.

I'm trying to get control of the visible aspect of OptionButtons made
from the Control Toolbar (ActiveX?).

I have lots of them.

I have written a sub that works but I would like to find out how to
refer to the buttons so I can do this better with a couple of loops.

Here's what I mean.

Sub hideStuff()
If (Worksheets("sheet1").Range("Z 5") > " ") Then
Worksheets("sheet2").OptionBut ton1.Visible = True
Worksheets("sheet2").OptionBut ton2.Visible = True
Worksheets("sheet2").OptionBut ton3.Visible = True
and so on
Else
Worksheets("sheet2").OptionBut ton1.Visible = False
Worksheets("sheet2").OptionBut ton2.Visible = False
Worksheets("sheet2").OptionBut ton3.Visible = False
and so on
end if
end sub

Now I would like to put this in a loop where I could use a string for
the name of the buttons like

Name = "OptionButton" & 1

But then how do I refer to the buttons? I've tried everything I can
think of.

Worksheets("sheet2").OptionBut ton(Name).Visible = True

Can someone tell me what the piece is that identifies the object type
in the line above?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Harold Graham     Answered On: Jan 24

I believe you need to access the oleobjects collection.

MsgBox Worksheets("Sheet1").OLEObjects("OptionButton1").Name

Should display the name of OptionButton1 which is ... er... "OptionButton1".

 
Answer #2    Answered By: Giovanna Silva     Answered On: Jan 24

That's just the kind of info I needed.

Using it, I was able to get the buttons  to go on and off.

Worksheets("Sheet1").OLEObjects("OptionButton1").Visible=False

for example.

All was well until I tried to also control  the Value for the buttons.

It should have been so easy.

Worksheets("Sheet1").OLEObjects("OptionButton1").Value=False

But I get an error telling me Value is not a property of the object.

I think the two kinds of OptionButtons from the Form and Control
toolboxs are getting mixed up or something. Is this discussed
anywhere that you know of?

Should we always use the controls from the forms toolbox even if the
books only talk about the buttons from the Control Toolbox?

This is probably something simple that I'm just missing.

 
Answer #3    Answered By: Aaliyah Khan     Answered On: Jan 24

try using this. dunno if it'll work as it is....didn't have
time to run it....

Dim objObject As OLEObject

If Worksheets("sheet1").Range("Z5")> " " Then
For Each objObject In Worksheets("sheet2").OLEObjects
If objObject.ProgId = "Forms.OptionButton.1" Then objObject.Visible =
True
Next
Else
For Each objObject In Worksheets("sheet2").OLEObjects
If objObject.ProgId = "Forms.OptionButton.1" Then objObject.Visible =
False
Next
End If

 
Didn't find what you were looking for? Find more on Question about objects and OptionButtons Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post