Excel VBA code throwing Run-time error '13': Type mismatch error-CodePudding

I can't figure out what it is about the second line in my VBA code in Excel (bullet point below) that is causing a "Run-time error '13': Type mismatch." In $B$1 I have created a drop-down list, and depending on what value is selected the corresponding macro is called to run (these are all macros to hide certain columns to curate custom views). The code is working but is constantly throwing this error that I have to clear. I researched and tried changing Target.Value to Target.Text but that didn't help?

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "$B$1") And (Target.Value = "Default View") Then Call Default_View

ElseIf (Target.Address = "$B$1") And (Target.Value = "Compact View") Then Call Compact_View

ElseIf (Target.Address = "$B$1") And (Target.Value = "Search View") Then Call Search_View

ElseIf (Target.Address = "$B$1") And (Target.Value = "Sessions Only") Then Call Sessions_Only

ElseIf (Target.Address = "$B$1") And (Target.Value = "Session Trends") Then Call Session_Trends

ElseIf (Target.Address = "$B$1") And (Target.Value = "Jump to Direct") Then Range("HN358").Select

Else: Call Show_All

End If

End Sub

CodePudding user response：

I suggest you separate the logic to first ask if the address is $B$1, then within that place your checks for target.value. Only query the value if you know the address is your control.

CodePudding user response：

If the user changes >1 cell then you can't compare Target.Value with a string. Maybe try something more like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then             'no need to repeat this test
        Select Case Target.Value
            Case "Default View": Default_View   'use of Call is deprecated
            Case "Compact View": Compact_View
            '...other cases
            Case Else: Show_All
        End Select
    End If
End Sub