Command button on/off

[raw]

Hi

I have a sheet with a couple of combo boxes and a command button.

Is there a way that would make the command button work only when a selection is made in a combo box

Eg combo box

Please select
Std bottom
Shaped finish
Straight or Shaped finish with trim

So if the bottom select is made (straight or shaped finish with trim) the command button when press will bring up a form i have created BUT if any of the other selections have been made pressing of the button will have no effect?

Thanks

 

[ExcelNovice]

I would assign a value when for each option in an isolated location on the spreadsheet and then use if statement in the macro code to determine which code is run when the command button is selected.

 

[Banker1]

You can use an if statment in your VBA referring to the state of the combo box.

Somthing like this:

Private Sub CommandButton1_Click()

If CheckBox1.Value = False And CheckBox2.Value = False Then
MsgBox Prompt:="Select at least 1 Check Box", Buttons:=vbInformation + vbOKOnly

ElseIf CheckBox1.Value = True And CheckBox2.Value = True Then
MsgBox Prompt:="Checkbox1 and Checkbox2 Selected", Buttons:=vbOKOnly

ElseIf CheckBox1.Value = True Then
MsgBox Prompt:="Checkbox1 Selected", Buttons:=vbOKOnly

Else
CheckBox2.Value = True
MsgBox Prompt:="Checkbox2 Selected", Buttons:=vbOKOnly

End If

End Sub

 

[Tazguy37]

What you could also try is to keep the command button disabled, until entries are made in both comboboxes. Something like this:

In the ThisWorkbook code module:

Private Sub Workbook_Open()
    Worksheets("Sheet1").CommandButton1.Enabled = False
    Worksheets("Sheet1").ComboBox1.ListFillRange = "A1:A10"
    Worksheets("Sheet1").ComboBox2.ListFillRange = "B1:B10"
End Sub

That disables the button and set the ranges of values that are in the comboboxes.

And in the sheet module where the comboboxes and command button are located:

Private Sub ComboBox1_Change()
    If ComboBox1.Value = "" Or ComboBox2.Value = "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub

Private Sub ComboBox2_Change()
    If ComboBox1.Value = "" Or ComboBox2.Value = "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub

That will make sure both buttons have something in them before the command button gets enabled. Please post back if you have any questions. Hope that helps!