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.
AG wrote:
[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