If Checkbox checked, return these values in comboxbox

[Hae]

Hello, I am a newbie trying to do some grand stuffs here...

I am trying to have "default" combo box selections based on if checkbox is checked. I have 1 checkbox (user form) and multiple combo boxes.

For example:
When unchecked, shows whatever the first value shows (I don't need to worry about this)

Then checked, shows the settings that I want in combobox

Could anyone help me with this? Thank you,

 

[John_w]

You haven't really given a full description of your setup, but assuming P3:P6 is the input range you have assigned to the 'Chair' combo box, and similar for the others, and you want to use the cell values 2 rows above those cells as the index of the selected value, and you have added the combo boxes in the order shown, try this code.

Add this code to a standard module:

Public Sub Set_Checkbox_OnAction()
    ActiveSheet.CheckBoxes(1).OnAction = "CheckBox_Click"
End Sub


Public Sub CheckBox_Click()

    Dim chb As CheckBox

    With ActiveSheet
        Set chb = .CheckBoxes(Application.Caller)
        If chb.Value = xlOn Then
            .DropDowns(1).ListIndex = .Range(.DropDowns(1).ListFillRange).Item(1).Offset(-2).Value
            .DropDowns(2).ListIndex = .Range(.DropDowns(2).ListFillRange).Item(1).Offset(-2).Value
            .DropDowns(3).ListIndex = .Range(.DropDowns(3).ListFillRange).Item(1).Offset(-2).Value
            .DropDowns(4).ListIndex = .Range(.DropDowns(4).ListFillRange).Item(1).Offset(-2).Value
        Else
            .DropDowns(1).ListIndex = 1
            .DropDowns(2).ListIndex = 1
            .DropDowns(3).ListIndex = 1
            .DropDowns(4).ListIndex = 1
        End If
    End With
   
End Sub

Next, run the Set_Checkbox_OnAction macro. This sets the 'OnAction' property of the checkbox, so that when you click the checkbox it automatically runs the CheckBox_Click macro, which sets each combo box value according to its input range and the cell value 2 rows above.

 

[Hae]

John,

You are exactly right. I have P3:P6 as my input range and P1 as my indexed value. So if Setting 2 is selected in the combobox, i would show 2 on cell P1.

Im currently at work but I will try this out and let you know!! Thank you for your help.

 

[Hae]

John,

I've tried the code out and getting an error message "unable to get the checkboxes property of the worksheet class" on the second set of codes. I only have 1 checkbox in this worksheet but I have other checkboxes in other sheets so I modified your code a little so it only activates on this checkbox. (I am a newbie so Im not sure if im doing this right either)

Also, turns out, for now I only need 1 combobox to change its list value on checkbox click so I only have 1 line of code regarding it right now

For this code, My checkbox is CheckBox 139. Do I need to change the number "1" below to "139"? - When I debugged this with (1), there was no error but error popped up with (139)
========================
Public Sub Set_Checkbox_OnAction()
Dim sht As Worksheet
Set sht = Worksheets("Multistage Worksheet")
sht.CheckBoxes(1).OnAction = "CheckBox_Click"
End Sub


Drop down I need is called Drop down 12
=========================
Private Sub CheckBox_Click()
Dim chb As CheckBox

With ActiveSheet
Set chb = .CheckBoxes(Application.Caller)
If chb.Vale = xlOn Then
.DropDowns(12).ListIndex = .Range(.DropDowns(12).ListFillRange).Item(1).Offset(-2).Value
Else
.DropDowns(12).ListIndex = 1
End If
End With
End Sub

Please let me know if you need any other information and Thank you for your help!

 

[Hae]

On second thought, Would it be easier to refer to the cell linked to my checkbox? I did "rename" my combobox to ADdropdown.

Public Sub CheckBox_Click()
Dim chb As CheckBox

With ActiveSheet
If Range("B4").Value = True Then
.DropDowns(ADdropdown).Value = "YES"
Else
.DropDowns(ADdropdown).Value = "NO"
End If
End With
End Sub

Something like this. But I am getting "unable to get the dropdowns property of the worksheet class" error..

 

[John_w]

You are exactly right. I have P3:P6 as my input range and P1 as my indexed value. So if Setting 2 is selected in the combobox, i would show 2 on cell P1.

Using a linked cell (P1) with the combo box will interfere with the way you want the checkbox to set the selected value in the combo box, because when you type a value (1-4) in P1 it will change the selected value in the combo box and vice versa. Therefore you should remove the linked cell setting in the combo box, which is what my code requires.

Also, turns out, for now I only need 1 combobox to change its list value on checkbox click so I only have 1 line of code regarding it right now

For this code, My checkbox is CheckBox 139. Do I need to change the number "1" below to "139"? - When I debugged this with (1), there was no error but error popped up with (139)
========================
Public Sub Set_Checkbox_OnAction()
Dim sht As Worksheet
Set sht = Worksheets("Multistage Worksheet")
sht.CheckBoxes(1).OnAction = "CheckBox_Click"
End Sub

The 1 in CheckBoxes(1) refers to the checkbox by its index number on the specific sheet, not its name. (1) is the first checkbox on the specific sheet, (2) is the second checkbox, etc.

To refer to CheckBox 139 by name, put its full name in double quotes:

sht.CheckBoxes("CheckBox 139").OnAction = "CheckBox_Click"

Drop down I need is called Drop down 12
=========================
Private Sub CheckBox_Click()
Dim chb As CheckBox

With ActiveSheet
Set chb = .CheckBoxes(Application.Caller)
If chb.Vale = xlOn Then
.DropDowns(12).ListIndex = .Range(.DropDowns(12).ListFillRange).Item(1).Offset(-2).Value
Else
.DropDowns(12).ListIndex = 1
End If
End With
End Sub

In that case, change the 3 occurences of 12 to "Drop Down 12".

 

[Hae]

Using a linked cell (P1) with the combo box will interfere with the way you want the checkbox to set the selected value in the combo box, because when you type a value (1-4) in P1 it will change the selected value in the combo box and vice versa. Therefore you should remove the linked cell setting in the combo box, which is what my code requires.


The 1 in CheckBoxes(1) refers to the checkbox by its index number on the specific sheet, not its name. (1) is the first checkbox on the specific sheet, (2) is the second checkbox, etc.

To refer to CheckBox 139 by name, put its full name in double quotes:

sht.CheckBoxes("CheckBox 139").OnAction = "CheckBox_Click"

In that case, change the 3 occurences of 12 to "Drop Down 12".

Thank you John,

I will try this out and thank you for all the great information!