Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 923
Default 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/



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 46
Default 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
_ _
^ąŻ^
--


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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/





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
My CheckBoxes and Option Buttons Grow/Shrink each time I click the Memphis Excel Discussion (Misc queries) 1 April 15th 09 08:51 PM
do some code after the sheet name is changed ARHangel Excel Discussion (Misc queries) 1 January 18th 08 11:39 AM
How can this code be changed Greg B Excel Discussion (Misc queries) 3 May 18th 05 04:32 PM
Code to tab from option button click Glen Millar Excel Programming 3 May 19th 04 02:14 AM
Code for Buttons Mike Excel Programming 3 August 21st 03 02:30 AM


All times are GMT +1. The time now is 03:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"