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

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

--
Message posted from http://www.ExcelForum.com

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

Try this

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

Cheers
Nigel

"neonangel " wrote in message
...
Hello All,

I am using some OptionButton option buttons in Excel and wish to change
their value (within VBA) without actually running the Click code
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 actually
selects the option button - not when I modify their values via code.

Any ideas?

Alex


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

Hi,

1) Put this in your Standard Code Module:

Public X As Boolean
Sub TEST()
X = True
ActiveSheet.OLEObjects("OptionButton1").Object.Val ue = 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
_ _
^¢¯^
--

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

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)

"neonangel " wrote in message
...
Thanks Nigel, but no luck unfortunately. I actually stumbled across that
some time earlier today but to my surprise it didn't appear to fix the
problem of the Click code executing! Any other ideas?


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com