VBA shortcut

  • Thread starter Thread starter Thrava
  • Start date Start date
T

Thrava

Hi folks,

How can I shortent this code?

i have an event procedure that triggers about 10
optionboxes to become disabled.

So if a click event occurs, then the following happens.

optionbox1.enabled=false
optionbox2.enabled=false
etc..

How can I write one statement... such as..
dim n as integer
for n=1 to 10
do
optionbox(n).enabled=false

or something like this.
is it possible?
Thanks
Trava
 
Hi Thrava,

If it is Forms buttons on a worksheet use

activesheet.optionbuttons.enabled=false


If it is control tolbox buttons, use

Dim oOLE As OLEObject

For Each oOLE In ActiveSheet.OLEObjects
If TypeOf oOLE.Object Is MSForms.OptionButton Then
oOLE.Enabled = False
End If
Next oOLE


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello Bob,
Thank you.
I only want to control a subset of the option buttons.
there are other objects on the sheet, such as combobox
etc.. as well.

How do I control only some of them with this sub that you
suggested?
Thanks
Thrava
 
Thrava,

That code only addresses the optionbuttons, not other controls.

What are you experiencing?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello again Bob,
That's correct. The problem (and its perhaps my fault for
not having explained completely), is that I don't want all
of the option boxes to be affected. Only a number of them.

That's why I was wondering if there is a way to only
affect a selected number of the option boxes.
is there?

Thanks
Thrava
 
Morning Thrava,

Yes you can access them independently, but you have to control it all. This
example shows how to select just 2 of a set and disable them

Dim opt As OptionButton
For Each opt In ActiveSheet.OptionButtons
If opt.Name = "Option Button 1" Or _
opt.Name = "Option Button 3" Then
opt.Enabled = False
End If
Next opt


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
thank you Bob.
That's what I thought.
I appreciate your help and responses.
Have a nice day
Thrava
 
Back
Top