option buttons run Click code when value is changed via VBA code

  • Thread starter Thread starter neonangel
  • Start date Start date
N

neonangel

Hello All,

I am using some OptionButton option buttons in Excel and wish to chang
their value (within VBA) without actually running the Click cod
associated with them.

When I use the following code to change the button value:

ActiveSheet.OLEObjects("opt1").Object.Value = True

The following code is automatically run:

Private Sub opt1_Click()
Call Display.ShowHideStocks(1, 2)
End Sub

However I only want the above code to run when the user actuall
selects the option button - not when I modify their values via code.

Any ideas?

Ale
 
Try this

Application.EnableEvents = False
ActiveSheet.OLEObjects("opt1").Object.Value = True
Application.EnableEvents = True

Cheers
Nigel
 
Thanks Nigel, but no luck unfortunately. I actually stumbled across tha
some time earlier today but to my surprise it didn't appear to fix th
problem of the Click code executing! Any other ideas
 
Hi,

1) Put this in your Standard Code Module:

Public X As Boolean
Sub TEST()
X = True
ActiveSheet.OLEObjects("OptionButton1").Object.Value = True
X = False
End Sub


2) Put this in your Worksheet Code Module:

Private Sub OptionButton1_Click()
If X = True Then Exit Sub

MsgBox "Actually selected"
........
........
End Sub



--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
If you want to stop these buttons executing, you need to create your own
global variable and check that, e.g.

If fExecute Then
... rest of code
EndIf

in the Click event, and set the flag outside to False. resetting when
appropriate.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Soo Cheon Jheong and Bob, a global variable was indeed a muc
easier way to accomplish what I was attempting to do than to worr
about dummy-setting the option button values and then setting the
back! I'd considered it before, but forgot how to set it up. Using
Public declaration was indeed the solution.

Regards,

Alex
 
Back
Top