Home |
Search |
Today's Posts |
#1
|
|||
|
|||
combo boxes on a form
Hi. I was wondering how to set up a combo box so that when a particular item
is chosen in the combo box, then certain check boxes are checked. I got some very helpful information last week on setting up the check boxes. I would like to take it one step further so that by choosing one item listed in the combo box, will auto check the appropriate check boxes. No macro please! thanks! |
#2
|
|||
|
|||
In the combobox click event, something like
Select Case Combobox1.value Case "value 1" : Me.checkbox1.Value = True Me.checkbox5.Value = True Case "value 2" : Me.checkbox3.Value = True Me.checkbox5.Value = True 'etc. End Select -- HTH Bob Phillips "Russell-stanely" wrote in message ... Hi. I was wondering how to set up a combo box so that when a particular item is chosen in the combo box, then certain check boxes are checked. I got some very helpful information last week on setting up the check boxes. I would like to take it one step further so that by choosing one item listed in the combo box, will auto check the appropriate check boxes. No macro please! thanks! |
#3
|
|||
|
|||
I am using the combo box from the "FORMS" toolbar, rather than the control
toolbox toolbar. I don't have Vb experience, so I was hoping for something simple........! Any suggestions? "Bob Phillips" wrote: In the combobox click event, something like Select Case Combobox1.value Case "value 1" : Me.checkbox1.Value = True Me.checkbox5.Value = True Case "value 2" : Me.checkbox3.Value = True Me.checkbox5.Value = True 'etc. End Select -- HTH Bob Phillips "Russell-stanely" wrote in message ... Hi. I was wondering how to set up a combo box so that when a particular item is chosen in the combo box, then certain check boxes are checked. I got some very helpful information last week on setting up the check boxes. I would like to take it one step further so that by choosing one item listed in the combo box, will auto check the appropriate check boxes. No macro please! thanks! |
#4
|
|||
|
|||
I don't think you shared enough info...
I put a dropdown on a worksheet with 4 cells as the input range (a1:a4). I put 4 checkboxes on the same worksheet. I could use this code (assigned to the dropdown) to check the corresponding checkbox (selecting the first option in the dropdown checks the first checkbox. 2nd option in the dropdown checks the 2nd checkbox, etc. Option Explicit Sub DDChange() Dim myDD As DropDown Set myDD = ActiveSheet.DropDowns(Application.Caller) If myDD.ListIndex < 1 Then Exit Sub 'nothing selected End If ActiveSheet.CheckBoxes("check box " & myDD.ListIndex).Value = xlOn End Sub ======= If you have multiple checkboxes that depend on the value chosen in the dropdown, you can utilize Bob's suggestion (slightly modified). (I used checkboxes from the Forms toolbar, too.) Option Explicit Sub DDChange() Dim myDD As DropDown Dim myStr As String Set myDD = ActiveSheet.DropDowns(Application.Caller) With myDD If .ListIndex < 1 Then Exit Sub 'nothing selected Else myStr = .List(.ListIndex) End If End With With ActiveSheet Select Case LCase(myStr) Case Is = "option1" .CheckBoxes("check box 1").Value = xlOn .CheckBoxes("check box 2").Value = xlOff .CheckBoxes("check box 3").Value = xlOn .CheckBoxes("check box 4").Value = xlOff Case Is = "option2" .CheckBoxes("check box 1").Value = xlOff .CheckBoxes("check box 2").Value = xlOn .CheckBoxes("check box 3").Value = xlOff .CheckBoxes("check box 4").Value = xlOn Case Is = "option3" .CheckBoxes("check box 1").Value = xlOff .CheckBoxes("check box 2").Value = xlOff .CheckBoxes("check box 3").Value = xlOff .CheckBoxes("check box 4").Value = xlOff Case Is = "option4" .CheckBoxes("check box 1").Value = xlOn .CheckBoxes("check box 2").Value = xlOn .CheckBoxes("check box 3").Value = xlOn .CheckBoxes("check box 4").Value = xlOn End Select End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste your code there. Then back to excel. Rightclick on the dropdown and select assign macro. And assign your macro (whatever version) to your dropdown. Russell-stanely wrote: Hi. I was wondering how to set up a combo box so that when a particular item is chosen in the combo box, then certain check boxes are checked. I got some very helpful information last week on setting up the check boxes. I would like to take it one step further so that by choosing one item listed in the combo box, will auto check the appropriate check boxes. No macro please! thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting Combo Boxes /Returning an Array | Excel Discussion (Misc queries) | |||
Combo boxes and Protection | Excel Discussion (Misc queries) | |||
connecting combo boxes to yield data in another cell. | Excel Discussion (Misc queries) | |||
dynamic combo boxes | Excel Worksheet Functions | |||
How do I autofill combo boxes with their destination cell? | Excel Worksheet Functions |