Consulting

Results 1 to 11 of 11

Thread: Bug with shortcut for macro??

  1. #1
    VBAX Regular
    Joined
    Apr 2009
    Posts
    59
    Location

    Bug with shortcut for macro??

    Hello,

    I have a spreadsheet that has a few macros. One of them is called by pressing Ctrl+z. This macro refreshes data on the spreadsheet from a database in a for loop such as:

    for i=1 to 20
    refresh(account(i))
    next i

    This usually works fine, but occasionally, pressing the shortcut will run the macro only for some accounts (like the first 12).
    However, this problem is usualy fixed by going into the code and launching the macro there.

    Does anyone know why this is happening? This has happened with a few different spreadsheets of mine

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Quote Originally Posted by frubeng
    Hello,

    I have a spreadsheet that has a few macros. One of them is called by pressing Ctrl+z. This macro refreshes data on the spreadsheet from a database in a for loop such as:

    for i=1 to 20
    refresh(account(i))
    next i

    This usually works fine, but occasionally, pressing the shortcut will run the macro only for some accounts (like the first 12).
    However, this problem is usualy fixed by going into the code and launching the macro there.

    Does anyone know why this is happening? This has happened with a few different spreadsheets of mine
    It will happen on any computer, Ctrl+Z is the undo command under windows, you need to change the shortcut key.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    VBAX Regular
    Joined
    Apr 2009
    Posts
    59
    Location
    Quote Originally Posted by Simon Lloyd
    It will happen on any computer, Ctrl+Z is the undo command under windows, you need to change the shortcut key.
    Are you sure about this? Because the it seems to me that the undo command is overwritten. Also it doesn explain why it would only run the for loop through part of the iterations... ?
    (and also that most of the time it runs fine)

    Thanks!
    Last edited by frubeng; 01-21-2010 at 08:56 AM.

  4. #4
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location
    Not sure why it is bugging out but I also use Control Z to launch a macro with no ill effects. However, if there is a possibility of it conflicting with a different function as mentioned by Simon, it may be a good idea to change it to prevent the problem which I will do on mine.

    Actually a shortcut to start a macro has limited options since so many are already in use by the application.

    I am interested to see what additional comments the experts here say could be causing your proboem.

    Good luck....

  5. #5
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    I would suggest assigning the macro to a button on the sheet, or to a button on a toolbar (2003 or earlier) or the Quick Access Toolbar (2007). This way you can test to see if it's the shortcut or something else.

  6. #6
    VBAX Regular
    Joined
    Apr 2009
    Posts
    59
    Location
    Quote Originally Posted by mbarron
    I would suggest assigning the macro to a button on the sheet, or to a button on a toolbar (2003 or earlier) or the Quick Access Toolbar (2007). This way you can test to see if it's the shortcut or something else.
    It is definitely the shortcut. I put in a break point at i=18 and the the breakpoint was never reached when using Ctrl+z, but it was reached normally when running the macro from where the code is...

  7. #7
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    Have you tried re-assigning the shortcut to another key combo like Ctrl+Shift+Z?
    Did you even try the button suggestion?
    Does running it from the VBE work all of the time - without fail?

  8. #8
    VBAX Regular
    Joined
    Apr 2009
    Posts
    59
    Location
    Quote Originally Posted by mbarron
    Have you tried re-assigning the shortcut to another key combo like Ctrl+Shift+Z?
    Did you even try the button suggestion?
    Does running it from the VBE work all of the time - without fail?
    Unfortunately it's very hard for me to replicate the error on demand, as right now it works fine, but wasn't last night

    Yes, running from the VBE code always works.

  9. #9
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Do you get any message when the code stops?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    VBAX Regular
    Joined
    Apr 2009
    Posts
    59
    Location
    Quote Originally Posted by mdmackillop
    Do you get any message when the code stops?
    No, in fact if i wasn't paying close attention i probably wouldnt notice that some accounts are just not being refreshed (as in the for loop doesnt continue on)

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You could try CodeCleaner.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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