CODEWITHSUNDEEP
exceldrop-down-menuvba
auston215
auston215

Reputation: 7

VBA Run-time error '13': Type mismatch

I'm trying to run macros through drop down list, but it wont run. Here is my view code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2:D12")) Is Nothing Then
    Select Case Range("D2:D12")
        Case "Production Steam?": Macro1
        Case "Production Other?": Macro2
        Case "Production Hydro?": Macro3
    End Select
End If
End Sub

Upvotes: 1

Views: 149

Answers (1)

user4039065
user4039065

Reputation:

Use another range object var to cycle through each of the cells in D2:D12 that have changed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2:D12")) Is Nothing Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim t As Range
        For Each t In Intersect(Target, Range("D2:D12"))
            Select Case t.Value
                Case "Production Steam?": Macro1
                Case "Production Other?": Macro2
                Case "Production Hydro?": Macro3
            End Select
        Next t
    End If
safe_exit:
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions