PDA

View Full Version : Solved: Option Buttons in Multipage Form



fredlo2010
06-03-2012, 09:37 AM
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.

Private Sub MultiPage1_Click(ByVal Index As Long)

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

End Sub



Thanks a lot

mikerickson
06-03-2012, 10:52 AM
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:
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

And then put this code this in the userform's code module
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

fredlo2010
06-04-2012, 03:19 PM
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

mikerickson
06-04-2012, 08:07 PM
This might cut down the number of lines
Dim oneControl as Object

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

fredlo2010
06-04-2012, 09:26 PM
Mike this works perfectly that's what a was looking for a collection of objects.

Thanks a lot

snb
06-05-2012, 04:35 AM
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.

fredlo2010
06-05-2012, 05:05 AM
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.

snb
06-05-2012, 06:12 AM
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.

fredlo2010
06-05-2012, 07:28 AM
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.

snb
06-05-2012, 08:04 AM
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....

fredlo2010
06-05-2012, 11:48 AM
OK here is the workbook with the form and the codes.

Here is the link to the file (https://dl.dropbox.com/u/30987064/Cookies.xlsm). I had to use dropbox because the file is a little big
Thanks