Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Subroutine Works but I get a Run Time Error 13

0

I'm a vba novice but working on it...

I have a subroutine within the "Names" worksheet that checks the value of a specific cell (selected cell in Column 5) and if it meets the criteria (contains a "V"), it copies an image from another worksheet (Images) and pastes it into a different cell (Column B) in the same row.  Everything works but I still get a RunTime Error '13' Type Mismatch.  I'm baffled.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim tr As Long

    tr = Target.Row

    If Target.Column = 5 And Target.Value = "V" Then

        Worksheets("Images").Shapes("Picture 1").Copy: Worksheets("Names").Range("B" & tr).PasteSpecial

End If

End Sub


Thanks for your quick response! I updated my Excel version (Home and Student 2021). I've also attached a simplified sample excel file with my VB code.  I've been playing around with it and it appears I only get that error when I click on a column (ie. A, B, C)? 

I could live with it, but another person using this workbook would get confused...

Also, I have another question related to this worksheet.  If I enter a "V" into column 5, it correctly pastes a flag image into column D same row, but what if I entered the "V" by mistake? I can delete the flag image itself, but I cannot delete the "V" in column 5.

Note that in the code, flagcopy logic was added later, after I was already getting the RunTime Error 13.

I hope I'm explaining this ok?

Answer
Discuss

Discussion

Hi CharlieB   Apologies- I posted an Answer then deleted it ehen I realised it was wrong (although it removed unnecessary retriggering of the event macro). 

Which version of Excel are you using? (Your code doesn't cause an error n 365. )

(Please add that to your Profile in the Forum)   

Please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show some existing data (and your macros). That nearly always saves us time and effort (recreating your problem) and means you are likely to get an answer quicker.
John_Ru (rep: 6142) Jul 28, '23 at 5:09 pm
Add to Discussion

Answers

0
Selected Answer

CharlieB

Instead of the Worksheet_SelectionChange event, I suggest you use the Worksheet_Change instead i.e. it acts when you change a value, not when you just move cell.

I changed the data validation so only "V" is permitted in columns E and H. I wasn't sure of the purpose of two Vets columns so the code makes the values the same. If you change one to V, the flag appears in B. Delete the V (in either column) and it will be gone.

I've added comments to help: 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tr As Long
    Dim flagcopy As Long
    Dim Pic As Shape

    On Error Resume Next

    ' Do nothing unless single cell changed in column E or H
    If Not (Target.Column = 5 Or Target.Column = 8) Or Target.CountLarge <> 1 Then Exit Sub

    ' stop even code retriggering itself
    Application.EnableEvents = False
    tr = Target.Row
    ' make both Vets columns the same
    Range("E" & tr).Value = Target.Value
    Range("H" & tr).Value = Target.Value
    ' first find (and delete) if there's a picture in column B already
    For Each Pic In Worksheets("Names").Shapes
        ' loop through like shapes and delete if in column B same row
        If Pic.Type = 13 And Pic.TopLeftCell.Address = Range("B" & tr).Address Then
           Pic.Delete
           flagcopy = 0
           Exit For
        End If
    Next Pic

    ' now check new value
    If Target.Value = "V" Then
        ' add a flag
        Worksheets("Images").Shapes("Picture 1").Copy: Worksheets("Names").Range("B" & tr).PasteSpecial
        flagcopy = 1

        ElseIf Target.Value <> "" Then
        ' reset other pasted values
        MsgBox "Looks like you pasted an illegal value (now reset to blank)"
        Exit Sub

    End If
    ' move to cell only if flag pasted
    If flagcopy = 1 Then Worksheets("Names").Range("B" & tr).Select
    Application.EnableEvents = True
End Sub

Don't have much time but hope this helps. If so, please mark this Answer as Seelected. If not, add to the discvussion below please.

Discuss

Discussion

Thank you. It works!
CharlieB (rep: 4) Aug 1, '23 at 8:32 pm
Glad that worked for you (and hope you can sort the flags v duplicate columns logic). Thanks for selecting my Answer CharlieB. 
John_Ru (rep: 6142) Aug 2, '23 at 3:22 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login