dropdownmenu automatically checks checkbox

Question

I have a question, i've tried to search the internet alot, but havent found a solution to help me.

Heres my problem: I have a dropdown menu in sheet3 (called ws_step3 in vba) at cell J3. The dropdown menu has 9 options, where 2 of those options automatically should enable a checkbox (lets call the checkbox "Coffeecup")

The 9 options are A,B,C etc.

Im looking for a VBA code that automatically checks that checkbox if 2 of the options are checked (lets say its C and F that checks the checkbox)

Im using Active X checkboxes, and using drop down menus

Hope anyone can help me.

TY in advance from a newcomer in VBA :-)
/Klaus

Edit #1 - Tried this first

Private Sub Worksheet_Calculate()  
    If ws_Step3.Range("J3").Value = "C" Then  
    ws_Step3.CheckBoxes("Coffeecup").Value = xlOn 
    Else  
    ws_Step3.CheckBoxes("Coffeecup").Value = xlOff  
    End If  
End Sub

Edit #2 - Credit to DDuffy for helping on this one - I already have this in my Private Sub Worksheet_Change(ByVal Target As Range) for J3

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$J$3" Then
    'Hvis værdien hedder "fremført cykelsti":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(2, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(3, 2).Value
End If

    'Hvis værdien hedder "Afkortet cykelsti":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(13, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(14, 2).Value
End If

    'Hvis værdien hedder "Venstresving fra langsiden af T-kryds":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(17, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(18, 2).Value
End If

    'Hvis værdien hedder "Cykelbane":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(21, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(22, 2).Value
End If

    'Hvis værdien hedder "Ingen cykelfaciliteter":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(27, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(28, 2).Value
End If

    'Hvis værdien hedder "Højresvingsshunt":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(31, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(32, 2).Value
End If

    'Hvis værdien hedder "Hollænderboks":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(42, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(43, 2).Value
End If

    'Hvis værdien hedder "Cykelsti i eget trace":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(46, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(47, 2).Value
End If

    'Hvis værdien hedder "Tilladt højresving for rødt":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(57, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(58, 2).Value
    End If

End If

End Sub

And DDuffys suggestion comes here (changed it to the real problem, no more beating around the bush)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False

On Error GoTo Errortrap


'~~> Change it to the relevant string with which you want to compare
StringToCheck1 = "Hoejresvingsshunt"
StringToCheck2 = "Tilladt Hoejresving for roedt"


If Not Intersect(Target, Range("J3")) Is Nothing Then
    '~~> Check for the cell value
    If Target.Value = StringToCheck1 Then
    'change checkbox value to true if it matches
    Worksheets("ws_Step3").HoejreD.Value = True
    ElseIf Target.Value = StringToCheck2 Then
    'change checkbox value to true if it matches
    Worksheets("ws_Step3").HoejreD.Value = True
    Else
    'change checkbox value to false if it doesn't match
    Worksheets("ws_Step3").HoejreD.Value = False
    End If
End If

LetsContinue:
   Application.EnableEvents = True
   Exit Sub
Errortrap:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

My question is now, how do i merge these to worksheet changes?

I have a image of my worksheet here: https://i.stack.imgur.com/IxdvY.jpg

What have you tried so far? The trying is the fun part. You want to think of your pseudo code, then translate as much of that as you can into VBA. so you'll want something along the lines of: If ws_steps.Text equals "C" and "F" then CheckBoxName.Value equals true. Try converting that to VBA and show us what you have. People on here don't tend to do your work for you, but there are many that will help if you get stuck. — DDuffy, May 03 '16 at 08:39
Forgot to mention that if you have choosen option C/F and want to reselect from your dropdown menu to any option A or B, the checkbox should be unchecked again, but only for option A or B, the rest should keep it selected if it already have been selected from chossing C/F. — Klaus, May 03 '16 at 08:43
Tried this: Private Sub Worksheet_Calculate() If ws_Step3.Range("J3").Value = "C" Then ws_Step3.CheckBoxes("Coffeecup").Value = xlOn Else ws_Step3.CheckBoxes("Coffeecup").Value = xlOff End If End Sub — Klaus, May 03 '16 at 08:48
You will be better off editing the question with what you have tried rather than in the comments. — Tim Wilkinson, May 03 '16 at 08:59
Without recreating your entire spreadsheet, this would be very difficult for me to test. however, i do believe that if you add everything from `If Target.Cells.Count > 1 Then Exit Sub` onwards (excluding the "End sub") and paste it in before the final End If in your code, it should work. — DDuffy, May 03 '16 at 11:28

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

credit to Marc L's question for giving the building blocks for this.

this should work, assuming its a data validation dropdown box.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

On Error GoTo Errortrap


'~~> Change it to the relevant string with which you want to compare
StringToCheck1 = "C"
StringToCheck2 = "F"


If Not Intersect(Target, Range("J3")) Is Nothing Then
    '~~> Check for the cell value
    If Target.Value = StringToCheck1 Then
      'change checkbox value to rue if it matches
       Worksheets("ws_Step3").Coffeecup.Value = True
       ElseIf Target.Value = StringToCheck2 Then
      'change checkbox value to true if it matches
       Worksheets("ws_Step3").Coffeecup.Value = True
       Else
      'change checkbox value to false if it doesn't match
       Worksheets("ws_Step3").Coffeecup.Value = False
    End If
End If

LetsContinue:
   Application.EnableEvents = True
   Exit Sub
Errortrap:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

this will change the check box to true (or ticked) when C or F is selected in the drop down.

EDIT

OK, think ive got it, (again, without recreating your original sheet or being able to read your comments, this is just "Should Work" territory).

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False

On Error GoTo Errortrap


'~~> Change it to the relevant string with which you want to compare
StringToCheck1 = "Hoejresvingsshunt"
StringToCheck2 = "Tilladt Hoejresving for roedt"


If Not Intersect(Target, Range("J3")) Is Nothing Then
    '~~> Check for the cell value
    If Target.Value = StringToCheck1 Then
    'change checkbox value to true if it matches
    Worksheets("ws_Step3").HoejreD.Value = True
    ElseIf Target.Value = StringToCheck2 Then
    'change checkbox value to true if it matches
    Worksheets("ws_Step3").HoejreD.Value = True
    Else
    'change checkbox value to false if it doesn't match
    Worksheets("ws_Step3").HoejreD.Value = False
    End If
    
        'Hvis værdien hedder "fremført cykelsti":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(2, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(3, 2).Value
    End If

    'Hvis værdien hedder "Afkortet cykelsti":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(13, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(14, 2).Value
    End If

    'Hvis værdien hedder "Venstresving fra langsiden af T-kryds":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(17, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(18, 2).Value
    End If

    'Hvis værdien hedder "Cykelbane":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(21, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(22, 2).Value
    End If

    'Hvis værdien hedder "Ingen cykelfaciliteter":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(27, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(28, 2).Value
    End If

    'Hvis værdien hedder "Højresvingsshunt":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(31, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(32, 2).Value
    End If

    'Hvis værdien hedder "Hollænderboks":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(42, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(43, 2).Value
    End If

    'Hvis værdien hedder "Cykelsti i eget trace":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(46, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(47, 2).Value
    End If

    'Hvis værdien hedder "Tilladt højresving for rødt":
    If ws_Step3.Cells(3, 10).Value = WS_DDL.Cells(57, 2).Value Then
    'Default value sættes til det første i dropdown
    ws_Step3.Cells(8, 12).Value = WS_DDL.Cells(58, 2).Value
    End If
    
End If

LetsContinue:
   Application.EnableEvents = True
   Exit Sub
Errortrap:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

hmmm, it won't work, trying to edit what i have done in my original question — Klaus, May 03 '16 at 10:20
when you have your VBA editor open, double click on your sheet name (e.g. "Sheet1(ws_Step3)") in the project window (top left) add the code there. make sure your dropdown list is in cell "J3" or change the reference in the code. make sure all the names are correct, i.e. Coffeecup etc. Also, was the dropdown list added using "Data Validation"? — DDuffy, May 03 '16 at 10:27
it needs to be added to the code behind the worksheet with the drop down on it, as it is checking for changes to the page. If the dropdown is Data Validation and it is an activeX check box, this "should" work. check your object names and ranges in the code match your workbook and ensure you have the code in the correct place *See image added to my answer* — DDuffy, May 03 '16 at 10:36
I already have a "Private Sub Worksheet_Change(ByVal Target As Range)" in my sheet3, does it need to come inside the same sub, or can they stand alone ? — Klaus, May 03 '16 at 10:37
Yes i did, but its not working, no error comes up. Maybe i should just accept that you have to actively go in and check the checkbox no matter what. — Klaus, May 03 '16 at 12:32
oh hang on... apologies, i dont speak german (assuming that the laguage... or dutch maybe), so your comments make no sense to me. but it basically looks like your existing code does exactly what my code does. Try removing your code altogether and just add the new code. Basically my code looks to see if the list box contains one of the two values, if it does, it ticks the box, if it doesnt, it unticks the box. — DDuffy, May 03 '16 at 13:24
what does the second part of your if statements change? (Sorry, trying to help while im at work) — DDuffy, May 03 '16 at 13:26
if you are able to translate your comments into english i should be able to help merge the two modules. i love a challenge... — DDuffy, May 03 '16 at 13:29

score 0 · Answer 2 · answered May 03 '16 at 10:07

0

I don't think you have "a dropdown menu in sheet3" but that you have a combobox that drops down and in which you can select something and that you use that as a menu.

With a combobox you can use the Change event to detect a selection. Then you obtain what has been selected and based on that you do your action.

Private Sub object_Change( )

where object is the name of your combobox.

answered May 03 '16 at 10:07

Paul Ogilvie

25,048
4
23
41

You are correct, my dropdown selections are located in another sheet, called "WS_DDL" I am not using a combobox as far as i know, i am using data validation on cell J3 in sheet3. The dropdown menu in J3 comes from the named range i provided in my dropdown sheet. Im not entirely sure what you mean with your solution, since im quite new to VBA. Can you elaborate on that? – Klaus May 03 '16 at 10:28

dropdownmenu automatically checks checkbox

2 Answers2

EDIT