VBA to reset dependent Combo Box cell link value

[Ngamia]

Scenario:
The user selects a region (Dropdown1) then gets to choose from the filtered product codes list (Dropdown4) to populate the sales data sheet. The problem is, when you change the region, the product code list remains at whatever row number had been set previously.

I need a macro that resets the value of a dependent Combo Box (Form Control) to 1 whenever the main Combo Box (Dropdown1) changes (irrespective of the change value).

The settings for the two boxes are shown below.
DropDown1 (Region)
INPUT RANGE: $S$2:$S$10
CELL LINK: $E$3

DropDown4 (Product Code)
INPUT RANGE: U:U
CELL LINK: $E$5

The values in the Input Ranges are simply hard-coded filtered lists based on the value in the cell link. For example, the data in column U is derived as follows:

 IF($E$3=4,FILTER(Product_Code, Region=$T$4))

I am using the following code to reset Dropdown 1 whenever the document is opened:

    Private Sub Workbook_Open()
        Worksheets("Sheet1").Range("E3") = 1
    End Sub

 

[James006]

Hi,

If you are using Form ComboBox in your worksheet ...

Sub ResetFormDropDown()
Dim dd As DropDown
Set dd = ActiveSheet.Shapes("Drop Down 1").OLEFormat.Object
dd.ListIndex = 1
End Sub

 

[Ngamia]

Thanks @James006. This is exactly what I needed. It works perfectly.

Being a VBA noob, it took me a while to figure out how to get it to work.
To assist those coming after me, this is what you do:

- Edit the code provided by @James006 so that ("Drop Down 1").OLEFormat.Object references the Combo Box you want to reset. In my case this was Dropdown4, so I changed the line to ("Drop Down 4").OLEFormat.Object

- Right click on the sheet with your dropdown and select "View Code" from the menu

- add the code above to the window that opens

- Right click on the main combo box (the one that controls the reset) and select "Assign Macro..."

- The list should now include the code you just added (.ResetFormDropDown)

- Click on the name (.ResetFormDropDown) to assign it to the Combo Box

- Click Ok.

I hope this is makes sense.

 

[James006]

Pleased to hear you have managed to solve your problem