shapes in row and column loop won't stop on loop criteria and errors

[xlr8urknowledge]

Hi all,
VBA dabbler here! I wrote this code to track training records and update actual signatures. The portion I am having difficulty with is I want to replace a signature shape (captured signature bitmap image) and not just add it over the previous one. to delete the shape I have it locating the cell that holds the shape in it by row and column. The column doesn't change but the row will. I wanted the column option in case I added more columns. I've stepped through the VBA code several times and for some reason when I install a copied sheet from a template sheet, the code will not complete and errors as it searches shapes on the sheet but when the loop criteria is met is does not delete the shape.
There may be a much better way to do this, but I don't know why it does not follow the directions I gave it. Non logical driving me nuts!!
Questions:

1. Is there an easier way to achieve the replacement of the shape rather than adding one on top of the other?
2. could I make the column fixed at 9 (see code example) and then just loop rows for 9 (see code example)?
3. what is the criteria VBA uses to decide what row and column to loop through? (I see it go to column 12 where the macro buttons are and then search all of the active rows then it goes to column 9 where the signatures are and loops through past row 9 then errors!???)

Thanks for any and all help!!
Happy 4th of July USA!
Ken

Sub SignatureQSM()
    Dim ActSheet As Worksheet
    Dim ActSheetName As String
    Dim Sh As Shape
    Dim pword As String
    Dim ADate As String
    
    
    On Error GoTo handler
    
    pword = Application.InputBox("Enter password", "Sign for Completed Training", Type:=2)
    If pword <> "xx" Then Exit Sub
    
    Set ActSheet = Sheets(ActiveSheet.Name)
    ActSheetName = ActiveSheet.Name
    Set ActSheet = Nothing
    
ActiveWorkbook.Unprotect Password:="xyz"
    
ActiveSheet.Unprotect Password:="zyx"
        
    For Each Sh In ActiveSheet.Shapes
        If Sh.TopLeftCell.Row = 9 And Sh.TopLeftCell.Column = 9 Then Sh.Delete
        Next Sh

Red code is where error happens and goes to handler after looping past.

 

[sijpie]

Your code should work. But it is messy and bug prone.

The nice thing about workbook and worksheet objects is that you can set them and so be sure that you work on the correct object.

So to make sure you don't mix things up with other sheets ((you mention you copied a sheet from a template. Are the signatures on more sheets?) I would set an object to the sheet I am working on. By using ActiveSheet as you do, I need to be very aware which is my active sheet.

Say that the signatures are on the sheet "Sign Off", then you could do:

set ActSheet = sheets("Sign Off")

and then from there on refer everything to this sheet object:

For Each Sh in ActSheet.Shapes

Also if you then need to work on ranges you refe these back to this object:

ActSheet.Range("B2") = xyz

If you have to work on more sheets, but the active sheet is the one you are needing to work on, then you can do what you did (set ActSheet = ActiveSheet), but do that at the very start of the macro.

Another tip when something is not working is to check what is happening. How sure are you that the topleftcell is in column 9, row 9?
You can check by debugging. (See my handy guide, link below on more debugging techniques)

    For Each Sh in ActSheet.Shapes
        Debug.Print Sh.Name & " " & Sh.TopLeftCell.Row & " " & Sh.TopLeftCell.Column
'         Sh.Delete 'Not for now until we have identified the correct ones
    Next Sh

good luck

 

[xlr8urknowledge]

Thank you for your reply.
So the intent of this sequence of events is to go to existing sheets and update the training of an individual on a specific item (a row specific information) I use the macro to search for the cell location of the shape (a bitmap of a signature) and delete it before resigning so I don't have a shape on a shape on a shape... from multiple sign offs. each row has the same macro assigned to its own button (per row - per training item.

press macro button, search for correct row and column for that shape of signature and delete it and place the new signature in its place.

The current issue:
A current sheet will operate correctly. however I copy a sheet for a new person and the row/column loop does not work as the loop does not go sequentially and errors out when it can't find (example: row = 9 and column = 9) because:
What I am seeing when I debug line by line is the code shows the rows stepping through column 12 shapes (Buttons) it will start at row 9 / column 12 step through sequentially but then skip row 21, then go to 22-30 and skip 31 & 32 go to 33. After 33 it goes to 21 the 31 then 32. Now it errors out, because there is nothing in column 9 to see as far as a shape.


questions:
What would cause the loop to go out of sequential rows?
Is there a way to see a list of shapes to determine why it goes out of sequential order of rows?
Is there a better way to just search a certain cell for a shape to delete it prior to installing a new signature?

Row is variable based on training requirement, but column is stable at 9. need to search column 9 for specific row to delete if there.

Thanks for any and all help.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...op-wont-stop-on-loop-criteria-and-errors.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[xlr8urknowledge]

Sorry didn't realize both sites were connected as I posted in both for better response possibilities.

 

[sijpie]

THe thing about cross-posting is not that the sites are connected, but that on each site people could be working on your answer, not knowing that it has already been solved on another site. So if you pose a question on two sites, just grab the links (URL's) and add them to your post. That way people can check what has been done.

 

[sijpie]

You need to understand the difference between looping through rows

For lR is 1 to 20
    Cells(lR, "C").Value = xyz
Next lR

or looping through objects, in your case Shapes.

For each shS in Shapes
     debug.print shS.Name
Next shS

These objects are held in object collections. So all the shapes on a sheet are held in a Shapes collection. And you can loop through each of them using the 'For each shS in Shapes' construction shown above. If in the VBA editor you press F2, the library will open. Search on 'Shapes' and you will see the list of properties and methods belonging to the Shapes collection.

Shapes get an internal number when you create them (when you paste your image into the sheet, for instance). This internal number has nothing to do with position or whatever. You see when you paste an image, Excel gives it a name, like Image 12, which gives an indication of the order in which they are held in the Shapes collection. So when you loop through the shapes, they are handled according to their internal number, not to position etc.