Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
I have a Frame with about 10 Optionbuttons. I am trying to figure out a way
such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
Each control has its own events because it is a separate object. If you
want to perform an action based on a click event of the control, the code must be tied to the control performing the event. So the short answer is you have to write code for each control. "Ayo" wrote in message ... I have a Frame with about 10 Optionbuttons. I am trying to figure out a way such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
I don't believe there is a way around having code for each button's event.
However, you can write a separate sub procedure for the one block of code you want them all to execute the same, then have each button's event just call that sub instead of repeating it 10 times. "Ayo" wrote: I have a Frame with about 10 Optionbuttons. I am trying to figure out a way such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
John Walkenbach explains how to do this using a class module:
http://spreadsheetpage.com/index.php...one_procedure/ You'll want to insert a class module in the workbook's project (Insert|class module in the VBE). It'll be named Class1 (unless you rename it or add more). I used Class1 in this example. This code goes into the class module: Option Explicit Public WithEvents OptBtnBoxGroup As MSForms.OptionButton Private Sub OptBtnBoxGroup_Change() MsgBox OptBtnBoxGroup.Name & " changed" & vbLf & OptBtnBoxGroup.GroupName End Sub (You may want to give nice groupnames to each of the optionbuttons to make life simpler.) Then inside the userform module: Option Explicit Dim OptBtnBoxes() As New Class1 Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() Dim ctl As Control Dim OptBtnBoxCtr As Long OptBtnBoxCtr = 0 For Each ctl In Me.Controls If TypeOf ctl Is MSForms.OptionButton Then OptBtnBoxCtr = OptBtnBoxCtr + 1 ReDim Preserve OptBtnBoxes(1 To OptBtnBoxCtr) Set OptBtnBoxes(OptBtnBoxCtr).OptBtnBoxGroup = ctl End If Next ctl End Sub Then show the userform and test it out. Ayo wrote: I have a Frame with about 10 Optionbuttons. I am trying to figure out a way such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
Thanks
"B Lynn B" wrote: I don't believe there is a way around having code for each button's event. However, you can write a separate sub procedure for the one block of code you want them all to execute the same, then have each button's event just call that sub instead of repeating it 10 times. "Ayo" wrote: I have a Frame with about 10 Optionbuttons. I am trying to figure out a way such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
yes this can be done . Are you using a userform or activeX controls on a
worksheet? "Ayo" wrote: I have a Frame with about 10 Optionbuttons. I am trying to figure out a way such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Option Buttons and Frame
Userform method:
there are three parts to this. We need a collection to hold the controls, an object to represent the controls and an event handler to respond... (1) To the VBAProject ad a CLASS MODULE, rename it clsControl. This name is used later, so be careful and make sure Option Explicit is on each module Add this code: Option Explicit Private WithEvents m_Control As MSForms.OptionButton Public Event Changed(ctrl As Control) Property Set Control(newControl As MSForms.Control) Set m_Control = newControl End Property Private Sub m_Control_Click() UserForm1.Changed m_Control End Sub (2) add a userform. I assume the default name to be Userform1 later. Drop any number of option buttons onto it. Leave the default name as "Option" as we use this in the code. Paste this code: Option Explicit Dim colControls As New Collection Dim ctControl As clsControl Sub Changed(ctrl As Control) MsgBox ctrl.Name & " :" & ctrl.Value End Sub Private Sub UserForm_Initialize() Dim ctrl As Control For Each ctrl In Controls If ctrl.Name Like "Option*" Then 'AddValues ctrl 'for demo Set ctControl = New clsControl Set ctControl.Control = ctrl colControls.Add ctControl End If Next End Sub (3) fuinanlly, but not necessary, add some code in a standard MODULE to launch the userform Sub ShowForm() Dim uf As UserForm1 Set uf = New UserForm1 uf.Show End Sub So the userform code loops through the controls. Any "option" buttons are set to the clsControl object and saved in the collection. when a control is clicked, the clsControl event call the userform's Changed procedure and you see the message box. Its extremely easy to adapt for ANY kind of control click YES if this was helpful "Patrick Molloy" wrote: yes this can be done . Are you using a userform or activeX controls on a worksheet? "Ayo" wrote: I have a Frame with about 10 Optionbuttons. I am trying to figure out a way such that when I click on any of the option buttons, something is done. I can't figure out how to do this without having to write 10 of this: Private Sub OptionButton1_Click() End Sub Is there a way to do it all in one procedure instead of 10? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Option Buttons disappear from the frame when not in design mode | New Users to Excel | |||
Accessing every option button on a frame | Excel Programming | |||
Enable target frame option | Excel Discussion (Misc queries) | |||
deselect all option buttons in a frame | Excel Programming | |||
multipage - option buttons disappear from frame in Excel 2003 | Excel Programming |