Solved: Option Buttons in Multipage Form

Hello,

Ok I have this user form that contains several pages. Each page contain a group of option buttons.

Is there a way to have only one option button selected at the time. If i check am option button in page 2, I want all options in all pages unchecked except for the one I just selected.

I thought I could achieve this by assigning the same GroupName to all the buttons, but that does not work ( I can't do that)

I was thinking in a way of deselecting all radio buttons when switching pages.?

This works but takes a lot of work, I have about 6 pages with at least 10 radio buttons each.

[VBA]Private Sub MultiPage1_Click(ByVal Index As Long)

UserForm1.Page1Button1 = false
UserForm1.Page1Button2 = false
UserForm1.Page1Button2 = false

End Sub
[/VBA]


Thanks a lot

Excel's option buttons do not "link" with option buttons that are on a different Page, or Frame.

This can be gotten around by using a custom object. With this proceedure, option buttons will be grouped according to their .GroupName property only, not by their container.

Insert a class module, and name it clsCrossParentOptionButton and put this code in that module:
[VBA]Public WithEvents CPOptionButton As MSForms.OptionButton

Private Sub cpOptionButton_Change()
Dim myUF As Object
Dim oneControl As MSForms.Control

Set myUF = Me.ParentUF
If myUF.ufEventsDisabled Then Exit Sub

myUF.ufEventsDisabled = True

For Each oneControl In myUF.Controls
With oneControl
If TypeName(oneControl) = "OptionButton" Then
If oneControl.GroupName = CPOptionButton.GroupName Then
.Value = (.Value And (.Parent.Name = CPOptionButton.Parent.Name))
End If
End If
End With
Next oneControl

myUF.ufEventsDisabled = False
End Sub

Property Get ParentUF() As Object
Set ParentUF = CPOptionButton.Parent
On Error Resume Next
Do
Set ParentUF = ParentUF.Parent
Loop Until Err
On Error GoTo 0
End Property
[/VBA]
And then put this code this in the userform's code module
[VBA]Public ufEventsDisabled As Boolean
Dim CPOptionButtons As Collection

Private Sub UserForm_Initialize()
Dim oneControl As Object
Dim oneCPOptionButton As clsCrossParentOptionButton

Set CPOptionButtons = New Collection

For Each oneControl In Me.Controls
If TypeName(oneControl) = "OptionButton" Then
Set oneCPOptionButton = New clsCrossParentOptionButton
Set oneCPOptionButton.CPOptionButton = oneControl
CPOptionButtons.Add oneCPOptionButton, Key:=oneControl.Name
End If
Next oneControl

Set oneCPOptionButton = Nothing
End Sub

Private Sub UserForm_Terminate()
Dim oneCPOptionButton As clsCrossParentOptionButton

For Each oneCPOptionButton In CPOptionButtons
Set oneCPOptionButton = Nothing
Next oneCPOptionButton

Set CPOptionButtons = Nothing
End Sub[/VBA]

Hi mike,

Thanks a lot for the answer. This is pro level. I haven't even used a class before.

I am planning to turn my workbook into an addin late on. I was wondering if its safe. I was skimming through a how to make an addin the other day and it said something about the "me." statement. But I cant remember.

BTW my code works but the bad think is it takes 40 lines. More or less the same I have in the click button with a if...elseif...end if statement.

Thanks a lot

This might cut down the number of lines
[vba]Dim oneControl as Object

For Each oneControl in Userform1.Controls
If TypeName(oneControl) = "OptionButton" Then
oneControl.Value = False
End If
Next oneControl[/vba]

Mike this works perfectly that's what a was looking for a collection of objects.

Thanks a lot

I'm curious why you want dependent optionbuttons on several pages in a multipage control.
I always use multipage to make a distinction between sets of controls.

Hi snb,

The reason why is organization. Each page is a category.The option buttons in the pages are flavors. I will put the cupcakes in page 1 and the cakes in page 2 and so on. Now you cannot pick more than a flavor or you cannot select more than one kind at the time.

I think that in your case I would use a combobox or listbox to select a cake or cupcake or ....; once that has been selected, I would only need x optionbuttons to choose a flavour.
Even if the number of flavours is dependent on the choice in the combobox/listbox it is very simple to accomplish.
It saves you a lot of pages, a lot of optionbuttons and code to reset all optionbuttons.

Thanks a lot for the hint snb. I thought of that, but there is a little rule I always like to follow for my excel file when I am not the end user: " short, simple, s...." :rotlaugh:

I want my users to see all the options right there. I don't know if you get what I am saying.On top of that I think it will increase usage speed.

Quote:

---

Originally Posted by fredlo2010

Thanks a lot for the hint snb. I thought of that, but there is a little rule I always like to follow for my excel file when I am not the end user: " short, simple, s...." :rotlaugh:

I want my users to see all the options right there. I don't know if you get what I am saying.On top of that I think it will increase usage speed.

---

That's why I am asking because I fear that unnecessary pages and too many optionsbuttons do not comply to short, nor simple nor speedy.
Post your sample and we'll test....

OK here is the workbook with the form and the codes.

Here is the link to the file. I had to use dropbox because the file is a little big
Thanks