+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    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:
    Is there an easier way to achieve the replacement of the shape rather than adding one on top of the other?
    could I make the column fixed at 9 (see code example) and then just loop rows for 9 (see code example)?
    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

    Code:
    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.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Try adding this line to confirm the location...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    Quote Originally Posted by dangelor View Post
    Try adding this line to confirm the location...
    Please Login or Register  to view this content.
    Thank you!
    where would this be added?

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Just before...
    Please Login or Register  to view this content.
    If the address is $I$9 it should delete.

  5. #5
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    Thanks, but it goes directly to error.

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    What line errors and what is the error?

  7. #7
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    there is no error message but it goes to the error handler and ends the sub.

  8. #8
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    disabled handler and the error is "runtime error 91 Object variable or with block variable not set" as the msgbox line is selected.

  9. #9
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    That would lead me to think that no shapes exist on the activesheet. You may want to change the activesheet reference to the actual worksheet containing the signature.

  10. #10
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    Sorry, that is not the issue as I ensured there was a shape and I get the same error.

  11. #11
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Let's try this...
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    Hi Dangalor,
    it does display count # but same issue.

  13. #13
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Is the activesheet actually unprotected?

    Any chance you could post a worksheet with the code?

  14. #14
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    Sorry for the delayed response.
    active sheet is unprotected as well as the workbook. (verified)

    Unfortunately due to private info the worksheets would have to be greatly altered to supply them here.
    Thank you anyways!

  15. #15
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Let's try one more thing... (changes in red)
    Please Login or Register  to view this content.
    Last edited by dangelor; 07-08-2019 at 05:00 PM.

  16. #16
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    No luck. I did find 1 issue. as long as I don't copy a new sheet in the macro works. once a copy a sheet from a template(tested to work) is added, the column row loop fails to find the row and column correctly.
    Not sure why when it is a straight copy and then rename the sheet. Then the macros does not work on any sheet.

  17. #17
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Just for grins, add this line...
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    03-20-2014
    Location
    SD, CA
    MS-Off Ver
    Excel 2003
    Posts
    13

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

    So what appears to be happening is the loop is not going in order for some reason. it will skip rows and later cycle through the skipped ones. at this point it will error out.

    The goal for this is simply to cycle through the rows in column 9 and see if a shape (signature) currently resides in that cell and delete it prior to signing it again.
    row 9, column 9 has no shape in it delete the void cell or is it has a shape, delete the shape.

    The issue is it will not cycle through the cell locations in order so it misses the target cell location. Weird!!
    Is there a way to list the sequence the loop is using? It is getting some master list of rows to go through.

    Simple answer is to go to cell location and delete any shape there rather than searching shapes. If none is there then do nothing and move on!!!

  19. #19
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    I believe they will cycle through them in the order they were created. To list the sequence, open the Immediate window and run this...
    Please Login or Register  to view this content.

  20. #20
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

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

    Try using this to delete the shape...
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Stop continuous loop with no errors
    By nicole041715 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-06-2017, 05:36 PM
  2. [SOLVED] VBA to loop through Shapes
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-20-2016, 04:33 AM
  3. [SOLVED] loop through shapes ?
    By Lukael in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2014, 08:11 AM
  4. [SOLVED] Move Two Shapes In The Same Loop
    By primed in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2014, 08:03 AM
  5. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  6. Loop a column on Sheet1 and loop a row on Sheet2 to find a match
    By johnnywinter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2009, 02:09 PM
  7. Advancing outer Loop Based on criteria of inner loop
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1