Bug with shortcut for macro??

[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 :(

[Simon Lloyd]

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.

[frubeng]

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!

[bdsii]

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

[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.

[frubeng]

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

[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?

[frubeng]

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.

[mdmackillop]

Do you get any message when the code stops?

[frubeng]

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)

[mdmackillop]

You could try CodeCleaner (http://www.appspro.com/Utilities/CodeCleaner.htm).