Run a Macro based on a Drop Down List (Error)

Question

Hi, I am trying to run some macros based on the value that I choose from a drop down list.

If I run the macros by themselves they all work perfectly, but when I try to make them run out of a list with the same names and everything it does not work. And I really do not know how to fix this.

This is the code I am using and here are the pictures of the error I get. I get it while changing the value on the list or simply by deleting the value on the cell.

Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("AN5:AR5")) Is Nothing Then

        Select Case Range("AN5:AR5")

            Case "Novia Flats": NoviaFlatsMacro

            Case "1101 Grand": GrandMacro

            Case "Art House": ArtHouseMacro

            Case "Exchange Place": ExchangePlaceMacro

            Case "Grove Station": GroveStationMacro

            Case "Liberty Towers": LibertyTowersMacro

            Case "Paulus Hook": PaulusHookMacro

            Case "Monte Carlo": MonteCarloMacro

            Case "One Broadway": OneBroadwayMacro

        End Select

    End If

    End Sub

Answer 1

I would suggest similar to this construct where AN5 is a DV List to choose from.

Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("AN5")) Is Nothing Then

        Select Case Target

            Case "Novia Flats": NoviaFlatsMacro

            Case "1101 Grand": GrandMacro

        End Select

    End If

End Sub

Sub NoviaFlatsMacro()

    MsgBox "NoviaFlats 12345"

End Sub

Sub GrandMacro()

    MsgBox "1101 Grand 67890"

End Sub

Gord

Answer 2

Hi CarlosRdrz,

Are you sure that you want to check the range of 4 cells for one entry?

What cell is linked to your drop-down list?

Answer 3

Thank you Gord, your suggestion helped me fix the problem as well.

Answer 4

Hi Jason,

Thank you for your question, at first I did not get what you were trying to ask be, but when I thought about it a little bit, I just left the first cell of the range I was watching (AN5) and it all works well now.

Thank you for your help.