Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Macro (Forms Toolbar)
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Macro (Forms Toolbar)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Macro (Forms Toolbar)
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 Dave Peterson wrote: 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: [original post snipped] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Button Macro (Forms Toolbar)
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 AG wrote: 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 Dave Peterson wrote: 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: [original post snipped] -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert comments on Option Button of Forms Toolbar | Excel Discussion (Misc queries) | |||
Option Buttons from the Forms Toolbar - Excel 2003 | Excel Worksheet Functions | |||
Can i use the button in forms toolbar and maneuver to differentt.. | Excel Worksheet Functions | |||
Option Button on Forms Toolbar | Excel Discussion (Misc queries) | |||
Option Button and forms | Excel Programming |