Macro keyboard shortcut problem

[Bob]

I assigned Ctrl-Shift-Z to the macro below. When I execute the macro via
Tools | Macro | Macros... | Run, everything works fine. But if I execute the
macro via the keyboard shortcut, the macro inexplicably stops right after
line 7. Has anyone else run into this problem and/or can explain why it
happens? Please note that "Rates.xls" is open prior to running the macro.

Any help would be greatly appreciated. Thanks.

1 - Sub CleanUp()
2 - Application.ScreenUpdating = False
3 - Application.Calculation = xlCalculationAutomatic
4 - fname = ActiveWorkbook.Name
5 - pname = ActiveWorkbook.FullName
6 - spath = Left(pname, Len(pname) - Len(fname))
7 - Workbooks.Open Filename:=spath & "Taxes.xls"
8 - Workbooks("Rates.xls").Activate
9 - ActiveSheet.Rows("1:5").Delete Shift:=xlUp
10 - ActiveSheet.Rows("2:2").Delete Shift:=xlUp
11 - more code...
55 - End Sub

 

[Dave Peterson]

If you open a workbook with the shift key held down, then excel thinks you don't
want to run the auto_open or workbook_open procedures.

And since your shortcut key includes the shift key, it's confusing excel and
excel thinks you want to stop.

Remove the shift key from your shortcut key and try it out.

FYI: ctrl-z is the same as Edit|Undo. You may want to choose a different key.

 

[Bob]

Dave,

I wish the answer was that simple, but I am not holding the shift key down
(or any other key for that matter) when I first open the "Rates.xls"
workbook. All I'm doing is:

1) Starting Excel
2) Opening "Rates.xls"
3) After "Rates.xls" has opened, pressing Ctrl-Shift-Z to start the macro.

But after pressing Ctrl-Shift-Z, the macro fails to make "Rates.xls" the
active workbook after it has opened "Taxes.xls", and then it stops once
"Taxes.xls" has been opened!

However, if I run the macro via Tools | Macro | Macros... | Run, then
"Rates.xls" becomes the active workbook after "Taxes.xls" has been opened (as
it should), and the macro proceeds just fine.

Bob

 

[Dave Peterson]

It's not the key you're holding down when you open rates.xls.

It's that you're using ctrl-shift-z (includes that shift key) and the macro that
you're running tries to open another workbook (taxes.xls).

The shortcut key for the macro and file|open in that macro are the two things
that are screwing it up. Remove one of them and you'll be ok. (I guessed you'd
want to keep opening taxes.xls <vbg>.)

 

[Bob]

I should have had a V-8! Forgive my ignorance. I now understand what you
were trying to say. I will try to find another key combination that doesn't
involve the Shift key. Thanks for all your help, Dave.

 

[Dave Peterson]

IIRC...

ctrl-l, ctrl-q, ctrl-t
are not used in Excel.

ctrl-e, ctrl-m, ctrl-j
are used in the VBE, but not excel.

I should have had a V-8! Forgive my ignorance. I now understand what you
were trying to say. I will try to find another key combination that doesn't
involve the Shift key. Thanks for all your help, Dave.

 

[Bob]

Dave,
Thanks again!!!
Bob

IIRC...

ctrl-l, ctrl-q, ctrl-t
are not used in Excel.

ctrl-e, ctrl-m, ctrl-j
are used in the VBE, but not excel.

 

[cla99009]

I've discovered a possible work-around to this problem. If you have a user
form that the macro calls prior to the open workbook command, it breaks the
shift problem and will continue to run the macro after the file opens.