Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    How to exit VBA loop

    I have developed, in conjunction with "Zeddy", a project that winds up as a shared workbook. It has just developed a problem where by it goes into an endless VBA loop checling for I don't knnow what!
    Is there any way I can stop the loop to turn the sharing off and access the file?
    cheers

    Phil Carter

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Without an idea where it's looping it's a bit hard to suggest.
    Are you able to make it loop or does it just do it when it feels like it?

    cheers, Paul

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    If the code is still running you can force a break by pressing Ctrl-Break on the keyboard.

    Failing that, you can kill off the application or process using the Task Manager
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul
    It gets into the loop on opening
    cheers

    Phil Carter

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    You can step through the code in the VBA IDE (it used to be Shift F10 to open the debugger) then press F5 to step through.
    Alternatively, put some messagebox followed by exit command to make the code break at some point, then let it run on it's own.

    cheers, Paul

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Paul
    I cannot get access to the code becsue it is a shared workbook and effectively locked
    cheers

    Phil Carter

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Andrew
    Yes I can kill it off using Task Manager but that does not give me access to the workbook to stop the Sharing
    cheers

    Phil Carter

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Phil

    Is your question "How do I stop a macro from running automatically on opening a Workbook?" or "How do I stop a workbook from being shared?"

    What is Zeddy? Is that relevant to why the workbook is shared?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    You can change the security settings in the spreadsheet program to prevent macros running.

    cheers, Paul

  10. #10
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Andrew
    Yes, how do I stop the macrto from running automatically on opening. If I can stop it running I can stop the sharing.

    "Zeddy", Richard, is the forum member who assisted me when I designed the training record spreadsheet
    cheers

    Phil Carter

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Hold the Shift key while opening the workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Thanks Rory
    Problem was a conflict with a misspelled staff name between the staff list and the code.

    Fixed however, there appears to be a conflict between Office 2010 and Office 2013. mmmm! maybe because I converted it the 2013 format
    cheers

    Phil Carter

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by bonriki View Post
    Fixed however, there appears to be a conflict between Office 2010 and Office 2013. mmmm! maybe because I converted it the 2013 format
    You'll have to be more specific, please.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Rory
    Thanks to your suggestion I managed to open the file and find the offending code and fix, but I did notice that the file when opened in 2013 was in "compatibility mode"
    cheers

    Phil Carter

  15. #15
    2 Star Lounger
    Join Date
    Feb 2013
    Posts
    158
    Thanks
    36
    Thanked 15 Times in 14 Posts
    "Every Problem you deal with today Is, the direct result of a solution to, a previous problem !"
    Werner Earhardt

Posting Permissions

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