combo boxes on a form

  • Thread starter Russell-stanely
  • Start date
R

Russell-stanely

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!
 
B

Bob Phillips

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
 
R

Russell-stanely

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?
 
D

Dave Peterson

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.
 
Top