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
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.
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.