Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Buttons and List Boxes
hi there
I need to know how to activate some option buttons (only 2 of them, assuming they are what's best) on a list box, so that depending on which option button the user selects, the list within the list box changes to reflect their choice, eg: User selects option button 1 (called "Fruit") Input range in listbox changes to reflect the list containing apples, pears, oranges etc User selects option button 2 (called "Cars") Input range in listbox changes to reflect the list containing Honda, Mazda, Porsche, Toyota etc The user then makes their selection, clicks ok and their choice is returned to the relevant cell.. appreciate any help cheers and regards Sue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Buttons and List Boxes
Here is a solution that is not terribly elegant, but easy!
Create two list boxes, one for each list. Make them the same size and place one directly over the other. Then create the option buttons and use these as their _Click() event procedures: Sub OptionButton1_Click() ActiveSheet.Shapes("List Box 1").Visible = True ActiveSheet.Shapes("List Box 2").Visible = False End Sub Sub OptionButton2_Click() ActiveSheet.Shapes("List Box 2").Visible = True ActiveSheet.Shapes("List Box 1").Visible = False End Sub Both list boxes should be functional, but one will always be hidden so to the user it will appear and function as if there is one box but the options change. "Sue" wrote: hi there I need to know how to activate some option buttons (only 2 of them, assuming they are what's best) on a list box, so that depending on which option button the user selects, the list within the list box changes to reflect their choice, eg: User selects option button 1 (called "Fruit") Input range in listbox changes to reflect the list containing apples, pears, oranges etc User selects option button 2 (called "Cars") Input range in listbox changes to reflect the list containing Honda, Mazda, Porsche, Toyota etc The user then makes their selection, clicks ok and their choice is returned to the relevant cell.. appreciate any help cheers and regards Sue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Buttons and List Boxes
Many thanks for that..bit of tweaking and it works perfectly... cheers.
sorry to ask, but i now have another question:.. the required info is returned to the respective cell via a vlookup (example below) however this is only working when ListBox 4 is the active listbox..it's not returning the relevant value when list box 5 is active: [Ranges are just a sample from about 50!] Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 33 Or Target.Row <= 44 And Target.Column = 2 Then Select Case Target.Value Case "YB" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'YB Codes'!YBCodes" Rng = "'YB Codes'!YBCodes" Case "TECHNOLOGY" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'NSE Codes'!NSECodes" Rng = "'NSE Codes'!NSECodes" Case "Please Select" DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "" Rng = "" End Select End If Sub DialogOK1() ListIndex = DialogSheets("ACCCode").ListBoxes("List box 4").ListIndex If Rng = "" Then Exit Sub Else mytext = Application.WorksheetFunction.VLookup((DialogSheet s("ACCCode").ListBoxes ("List box 4").List(ListIndex)), Range(Rng), 2, False) ActiveCell.FormulaR1C1 = mytext End If ActiveCell.Offset(0, 1).Select End Sub what I want it to do is: "if list box 4 is selected, then vlookup against this range and return relevant value" OR "if list box 5 is selected, vlookup against that range and return relevant value" I know it's probably simple, but I'm having difficulty working out how to write this...and it's now 9.20pm and I'm dying to go home....(starving!) again, would really appreciate some guidance if you're able to help here.. kind regards Sue Sydney, Australia / London, UK *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Group Boxes Around Option Buttons | Excel Worksheet Functions | |||
Check Boxes or Option Buttons | Excel Discussion (Misc queries) | |||
option buttons or check boxes | New Users to Excel | |||
Form Option Buttons and Combo Boxes in VBA | Excel Programming | |||
Check Boxes and Option Buttons | Excel Programming |