Option Button Macro (Forms Toolbar)

[AG]

[XL2002 and soon XL2003 on Win2000SP2]

I require some assistance with a macro I have attempted to write to
unhide certain rows when one of three option buttons (OB) is selected.
Where I am running into problems is trying to logically determine the
code necessary to add when I have added additional groups of OB as well
as what happens/needs to happen once the rows are unhidden.

Specifically, my form is comprised of 2 sections; the 1st section is
composed of 6 groups of 3 option buttons (each group of OB corresponds
to one column of data (choices are Canada/US/Other). When a user selects
the Other option, a range of rows is unhidden underneath the 1st section
the for the user to key foreign exchange rates (note that once the user
has at least one Other OB selected, the same rows will apply to all 6
columns). The 2nd section is just a duplicate of the first allowing the
user a total of 12 possible items to key. All groups are functioning
independently and working well. My macro works great for the first set
of OB (posted below WITH confusing issues commented out) but I an
confused as to the following:

----------------------------------------------------
Sub OptionButtonItem1to6_Click()
With Sheet4.DrawingObjects("Option Button 163")
If Sheet4.DrawingObjects("Option Button 163").Value = 1 Then
Range("INTL_FX_1to6").EntireRow.Hidden = False
Else
Range("INTL_FX_1to6").EntireRow.Hidden = True
'If Sheet4.DrawingObjects("Option Button 161").Value = 1 Then
' Range("INTL_FX_1to6").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 162").Value = 2 Then
' Range("INTL_FX_1to6").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 163").Value = 3 Then
' Range("INTL_FX_1to6").EntireRow.Hidden = False
End If
End With
End Sub

Sub OptionButtonItem7to12_Click()
With Sheet4.DrawingObjects("Option Button 196")
If Sheet4.DrawingObjects("Option Button 196").Value = 1 Then
Range("INTL_FX_7to12").EntireRow.Hidden = False
Else
Range("INTL_FX_7to12").EntireRow.Hidden = True
'If Sheet4.DrawingObjects("Option Button 196").Value = 1 Then
' Range("INTL_FX_7to12").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 196").Value = 2 Then
' Range("INTL_FX_7to12").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 196").Value = 3 Then
' Range("INTL_FX_7to12").EntireRow.Hidden = False
End If
End With
End Sub
----------------------------------------------------

1. how do I loop the macro through the the first six groups of OB to
reflect if a user has selected the 2-6 group of OB without copying and
pasting for each "Other" OB from each of the 12 sets? In order words,
what must I add to the macro to ensure that the unhidden range is
visible if either 1 or all or any combination of the "Other" OB is
selected (I assume that once a user has selected an Other OB, further
selections of the Other OB within the macro do nothing).

2. once the macro has been updated for (1) above, can it be cloned for
the second section?

3. does it seem logical to have the FX values the user keys in (once an
Other OB has been selected) cleared if a later OB from that group is
chosen. I understand how to do that with one group of OB, but given my
question in (1), will that require significant coding?

Thanks in advance,
Dylan

 

[Dave Peterson]

I'm not sure I quite understand, but maybe you can get some ideas out of this:

First, I'd create one group of 3 option buttons with a groupbox around it.

Give each of the optionbuttons a nice caption--make sure one is Other.

Name the groupbox nice: GB1to6 (this will be used later).

Now assign each option button this macro:

Option Explicit
Sub testme()

Dim myOB As OptionButton
Dim myGroupName As String

With ActiveSheet
Set myOB = .OptionButtons(Application.Caller)

myGroupName = Mid(myOB.GroupBox.Name, 3)

If LCase(myOB.Caption) = "other" Then
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = False
Else
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = True
End If
End With

End Sub

Show the drawing toolbar and click on the arrow icon. Select the groupbox and
all 3 option buttons.

ctrl-drag them (or copy|paste) to where the second set will be.
rename that groupbox to: GB7to12

and again and again until your done (GB31to36).

By using a nice name, we can strip the leading GB and leave the portion that the
range name is based on.

===
To rename a groupbox. Just rightclick on it (sometimes easier than trying to
select it) and type that name in the namebox (to the left of the formulabar) and
hit enter.

[XL2002 and soon XL2003 on Win2000SP2]

I require some assistance with a macro I have attempted to write to
unhide certain rows when one of three option buttons (OB) is selected.
Where I am running into problems is trying to logically determine the
code necessary to add when I have added additional groups of OB as well
as what happens/needs to happen once the rows are unhidden.

Specifically, my form is comprised of 2 sections; the 1st section is
composed of 6 groups of 3 option buttons (each group of OB corresponds
to one column of data (choices are Canada/US/Other). When a user selects
the Other option, a range of rows is unhidden underneath the 1st section
the for the user to key foreign exchange rates (note that once the user
has at least one Other OB selected, the same rows will apply to all 6
columns). The 2nd section is just a duplicate of the first allowing the
user a total of 12 possible items to key. All groups are functioning
independently and working well. My macro works great for the first set
of OB (posted below WITH confusing issues commented out) but I an
confused as to the following:

----------------------------------------------------
Sub OptionButtonItem1to6_Click()
With Sheet4.DrawingObjects("Option Button 163")
If Sheet4.DrawingObjects("Option Button 163").Value = 1 Then
Range("INTL_FX_1to6").EntireRow.Hidden = False
Else
Range("INTL_FX_1to6").EntireRow.Hidden = True
'If Sheet4.DrawingObjects("Option Button 161").Value = 1 Then
' Range("INTL_FX_1to6").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 162").Value = 2 Then
' Range("INTL_FX_1to6").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 163").Value = 3 Then
' Range("INTL_FX_1to6").EntireRow.Hidden = False
End If
End With
End Sub

Sub OptionButtonItem7to12_Click()
With Sheet4.DrawingObjects("Option Button 196")
If Sheet4.DrawingObjects("Option Button 196").Value = 1 Then
Range("INTL_FX_7to12").EntireRow.Hidden = False
Else
Range("INTL_FX_7to12").EntireRow.Hidden = True
'If Sheet4.DrawingObjects("Option Button 196").Value = 1 Then
' Range("INTL_FX_7to12").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 196").Value = 2 Then
' Range("INTL_FX_7to12").EntireRow.Hidden = True
' ElseIf Sheet4.DrawingObjects("Option Button 196").Value = 3 Then
' Range("INTL_FX_7to12").EntireRow.Hidden = False
End If
End With
End Sub
----------------------------------------------------

1. how do I loop the macro through the the first six groups of OB to
reflect if a user has selected the 2-6 group of OB without copying and
pasting for each "Other" OB from each of the 12 sets? In order words,
what must I add to the macro to ensure that the unhidden range is
visible if either 1 or all or any combination of the "Other" OB is
selected (I assume that once a user has selected an Other OB, further
selections of the Other OB within the macro do nothing).

2. once the macro has been updated for (1) above, can it be cloned for
the second section?

3. does it seem logical to have the FX values the user keys in (once an
Other OB has been selected) cleared if a later OB from that group is
chosen. I understand how to do that with one group of OB, but given my
question in (1), will that require significant coding?

Thanks in advance,
Dylan

 

[AG]

Dave,

Thanks for replying; after re-reading my verbage my explanation was not
as clear as it could have been.

Each group of three option buttons (Canada/US/Other) relates to one
column of possible data (top section of the form has 6 groups of three
and the bottom section has six groups of three as well - a total of 12
possible Other sections to key). The intent is that any one of the Other
buttons from any group of the three will unhide 5 rows of area where the
user keys in data (the top section has its own 5 rows and the bottom
section has its own 5 rows).

The problem I face is figuring out how to ensure that when any 1 of the
6 top section Other buttons is selected the "top" five rows is unhidden
and the same for the bottom section with its unique 5 rows. I thought I
could code each group of 3 option buttons but I ran into problems
because once any second non-Other button was selected, the rows would
hide even though the first Other button was selected and the user keyed
data (particularly bad given that I would like to have the code clear
the 5 cells of data from a column if the user selects Canada/US after
selecting Other and keying data.

Long winded again, but perhaps this captures the issue is greater detail
and clarity.

Thanks in advance,
Dylan

I'm not sure I quite understand, but maybe you can get some ideas out of this:

First, I'd create one group of 3 option buttons with a groupbox around it.

Give each of the optionbuttons a nice caption--make sure one is Other.

Name the groupbox nice: GB1to6 (this will be used later).

Now assign each option button this macro:

Option Explicit
Sub testme()

Dim myOB As OptionButton
Dim myGroupName As String

With ActiveSheet
Set myOB = .OptionButtons(Application.Caller)

myGroupName = Mid(myOB.GroupBox.Name, 3)

If LCase(myOB.Caption) = "other" Then
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = False
Else
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = True
End If
End With

End Sub

Show the drawing toolbar and click on the arrow icon. Select the groupbox and
all 3 option buttons.

ctrl-drag them (or copy|paste) to where the second set will be.
rename that groupbox to: GB7to12

and again and again until your done (GB31to36).

By using a nice name, we can strip the leading GB and leave the portion that the
range name is based on.

===
To rename a groupbox. Just rightclick on it (sometimes easier than trying to
select it) and type that name in the namebox (to the left of the formulabar) and
hit enter.

[original post snipped]

 

[Dave Peterson]

Maybe you could just look at those "other" option buttons:

Option Explicit
Sub testme()

Dim myOB As OptionButton
Dim myGroupName As String
Dim AtLeastOneOtherOBWasUsed As Boolean

With ActiveSheet
Set myOB = .OptionButtons(Application.Caller)

myGroupName = Mid(myOB.GroupBox.Name, 3)

If LCase(myOB.Caption) = "other" Then
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = False
Else
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = True
End If

AtLeastOneOtherOBWasUsed = False
For Each myOB In .OptionButtons
If LCase(myOB.Caption) = "other" Then
If myOB.Value = xlOn Then
AtLeastOneOtherOBWasUsed = True
Exit For
End If
End If
Next myOB

.Range("whateveryounamedit").EntireRow.Visible _
= AtLeastOneOtherOBWasUsed

End With

End Sub

Dave,

Thanks for replying; after re-reading my verbage my explanation was not
as clear as it could have been.

Each group of three option buttons (Canada/US/Other) relates to one
column of possible data (top section of the form has 6 groups of three
and the bottom section has six groups of three as well - a total of 12
possible Other sections to key). The intent is that any one of the Other
buttons from any group of the three will unhide 5 rows of area where the
user keys in data (the top section has its own 5 rows and the bottom
section has its own 5 rows).

The problem I face is figuring out how to ensure that when any 1 of the
6 top section Other buttons is selected the "top" five rows is unhidden
and the same for the bottom section with its unique 5 rows. I thought I
could code each group of 3 option buttons but I ran into problems
because once any second non-Other button was selected, the rows would
hide even though the first Other button was selected and the user keyed
data (particularly bad given that I would like to have the code clear
the 5 cells of data from a column if the user selects Canada/US after
selecting Other and keying data.

Long winded again, but perhaps this captures the issue is greater detail
and clarity.

Thanks in advance,
Dylan

I'm not sure I quite understand, but maybe you can get some ideas out of this:

First, I'd create one group of 3 option buttons with a groupbox around it.

Give each of the optionbuttons a nice caption--make sure one is Other.

Name the groupbox nice: GB1to6 (this will be used later).

Now assign each option button this macro:

Option Explicit
Sub testme()

Dim myOB As OptionButton
Dim myGroupName As String

With ActiveSheet
Set myOB = .OptionButtons(Application.Caller)

myGroupName = Mid(myOB.GroupBox.Name, 3)

If LCase(myOB.Caption) = "other" Then
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = False
Else
.Range("INTL_FX_" & myGroupName).EntireRow.Hidden = True
End If
End With

End Sub

Show the drawing toolbar and click on the arrow icon. Select the groupbox and
all 3 option buttons.

ctrl-drag them (or copy|paste) to where the second set will be.
rename that groupbox to: GB7to12

and again and again until your done (GB31to36).

By using a nice name, we can strip the leading GB and leave the portion that the
range name is based on.

===
To rename a groupbox. Just rightclick on it (sometimes easier than trying to
select it) and type that name in the namebox (to the left of the formulabar) and
hit enter.

[original post snipped]