CODEWITHSUNDEEP
excelvbacomboboxdropdown
e_conomics
e_conomics

Reputation: 86

Trouble with Form Control dropdown box

I am having some trouble creating a dependent drop down box, where based on the value of the first dropdown, either a list is returned or a value is returned:

My first drop down ("Drop Down 6") is comprised of two options (a named range)

LU Classification:
        CFR
        DCR

The code for my second drop down ("Drop Down 11") is below:

'Declare variables
Dim wb As Workbook
Dim ws As Worksheet
Dim c_list As Worksheet

'Assign variables
Set wb = ThisWorkbook
Set ws = ActiveSheet
Set c_list = wb.Worksheets("C_List")
      
'DropDown level 1
Dim dd As DropDown
Set dd = ws.Shapes("Drop Down 6").OLEFormat.Object

'DropDown level 2
Dim dd2 As DropDown
Set dd2 = ws.Shapes("Drop Down 11").OLEFormat.Object

'DropDown level 2 values
With dd2
    If dd.Value = 1 Then
        dd2.ListFillRange = "LU_BBP"
    Else
        dd2.ListFillRange = "Packer"
    End If
End With

End Sub

My if statement doesn't seem to be working, where a list should be returned, when I select CFR on the first drop down.

Any thoughts?

EDIT: If I run through line by line in the VBA editor, the drop down does update, however, when simply using the drop down the change doesn't carry over.

Upvotes: 2

Views: 487

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You need to do this slightly differently.

Paste this code in the module

Option Explicit

Sub DropDown6_Change()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim dd As DropDown
    Set dd = ws.Shapes("Drop Down 6").OLEFormat.Object

    Dim dd2 As DropDown
    Set dd2 = ws.Shapes("Drop Down 11").OLEFormat.Object
    
    If dd.Value = 1 Then
        dd2.ListFillRange = "LU_BBP"
    Else
        dd2.ListFillRange = "Packer"
    End If
End Sub

Now right click on Drop Down 6 and click on assign macro. Assign DropDown6_Change to this drop down and you are done :)

In Action (A demo)

enter image description here

Upvotes: 1

Related Questions