Button action based on drop down list value

**derics** · 02-28-2018, 05:06 AM

I've got a sheet where if a particular value is chosen from drop down list some cells are filled with vlookup value. Despite filling in some cells with the formula I would like it to change the action of existing button.

The button is supposed to open a file, and different file should be opened based on the drop down list value (the file that is being used in the vlookup formula).

Is it possible in excel with a button or another solution is necessary?

**kev_** · 02-28-2018, 10:32 AM

- place all 3 files in the same folder
- open the masterfile (enabling macros)
- cell A1 should already contain the correct path to the files
- select either Lookup1 or Lookup2 in cell B1 and click RUN
- values in B2 to B5 will change

Code is in the sheet module

Private Sub ChangeLookup(ByVal Target As Range)
Dim fPath As String:        fPath = ThisWorkbook.Path & "\"
Dim wb
With Target
    wb = .Value
    If .Address = "$B$1" And wb <> "Choose" Then
            wb = fPath & wb & ".xlsx"
            Set wb = Workbooks.Open(wb)
            wb.Close False
    End If
End With
End Sub

Private Sub CommandButton1_Click()
    ChangeLookup Range("B1")
End Sub