Create dropdown list in excel that displays different values from the same lookup table

Question

In Excel, I'm attempting to make a drop-down list where, depending on which option is selected, the text will show a different value in relation to a table on another sheet. In my case, I want the choice to display a more widely used site name before switching to displaying the StationID. Although I tried the following VB code, I was unable to make it work for my example. All I can think of that might be causing a problem is the fact that these are in the same workbook but on different worksheets. Even after changing the code and attempting to establish a variable for the lookup table on a different worksheet, nothing changed.

Here is my Code :

Private Sub Worksheet_Change(ByVal Target As Range)
'Created By Excel 10 Tutorial
    siteName = Target.Value
    defRange = ActiveWorkbook.Sheets(“LookUpTable”).Range(“WISKILookupTable”)
    If Target.Column = 1 Then
        StationNum = Application.VLookup(siteName, defRange, 2, False)
        If Not IsError(StationNum) Then
            Target.Value = StationNum
        End If
    End If
End Sub

Answer 1

Looks like the Worksheet_Change() sub needs two edits:

Dim defRange As Range   ' Without this statement, defRange is a variant type, not a range

And add a Set statement to defRange =:

Set defRange = ActiveWorkbook.Sheets(“LookUpTable”).Range(“WISKILookupTable”)

Hence, the defRange variable now refers to the actual range. If not, the values in that range are used for defRange, which is not what you want.

Also, it's generally a good idea to include an Option Explicit declaration on the module's initial line. That will alert Excel to problems involving variable types and will detect the Set statement that is missing.

You will be forced to Dim every variable by Option Explicit, but that's also best practice.