Copy and Paste causing Run-Time Error '13'

[cornks]

Hi,

First time posting to the board. Apologies if I make any errors or break the forum etiquette.

I have a problem with some code that I have written and I am fairly new to VBA. I wrote some code so that if the cell A5 had a Yes or a No in it, then some columns would hide/unhide. (Cell A5 is a validated list) The code itself works fine. The issue is that every time that I copy and upon pasting in that sheet in another cell (say cell G16) I get:

Run-Time Error '13'
Type mismatch

Can somebody point me in the right direction? My current code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$E$5" And Target = "Yes" Then
Columns("G:I").EntireColumn.Hidden = False
Columns("AI:DF").EntireColumn.Hidden = False
Columns("EG:HE").EntireColumn.Hidden = False
ElseIf Target.Address = "$E$5" And Target = "No" Then
Columns("G:I").EntireColumn.Hidden = True
Columns("AI:DF").EntireColumn.Hidden = True
Columns("EG:HE").EntireColumn.Hidden = True
End If

Application.EnableEvents = True
End Sub


Thanks Heaps
cornks

 

[diddi]

hi and welcome to the board.

can i suggest you step through your macro by placing a stop here:

Application.EnableEvents = False


and hitting F8 to see where the error is being generated. then get back to us

 

[cornks]

Thanks for repsonding. Forgive me, today is my first day at trying to learn VBA. (I've been putting it off for years, but I couldn't any longer). I'm not sure what you mean by:

"by placing a stop here"

What I can tell you is if I hit debug. The following bit of code is highlighted in yellow:

If Target.Address = "$E$5" And Target = "Yes" Then

At this point I'm lost.

Thanks again.

Kind Regards,
cornks

 

[diddi]

see if this fixes it

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Target.Address = "$E$5" And Target.value = "Yes" Then
        Columns("G:I").EntireColumn.Hidden = False
        Columns("AI:DF").EntireColumn.Hidden = False
        Columns("EG:HE").EntireColumn.Hidden = False
    Elseif Target.Address = "$E$5" And Target.value = "No" Then
        Columns("G:I").EntireColumn.Hidden = True
        Columns("AI:DF").EntireColumn.Hidden = True
        Columns("EG:HE").EntireColumn.Hidden = True
    End If
    Application.EnableEvents = True
End Sub

 

[diddi]

or maybe this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$E$5" then exit sub
    Application.EnableEvents = False

     if Target.value = "Yes" Then
        Columns("G:I").EntireColumn.Hidden = False
        Columns("AI:DF").EntireColumn.Hidden = False
        Columns("EG:HE").EntireColumn.Hidden = False
    Elseif Target.value = "No" Then
        Columns("G:I").EntireColumn.Hidden = True
        Columns("AI:DF").EntireColumn.Hidden = True
        Columns("EG:HE").EntireColumn.Hidden = True
    End If
    Application.EnableEvents = True
End Sub

 

[cornks]

Thank you so much. That has worked a treat!!!

Just so I understand and learn from the experience, by placing the <> in the statement, and then the "then exit sub". You have told excel to only concentrate on cell A5 otherwise the macro is not relevant.

Thanks again.
cornks

 

[diddi]

If Target.Address <> "$E$5" then exit sub

if the target address (the cell you pasted into) IS NOT equal to "E5" then exit the subroutine and do nothing

 

[Dryver14]

just so I understand this you can not say
target address =

you have to say target address <>

 

[cornks]

Thank you again.
cornks