Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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. Bob wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
Dave,
Thanks again!!! Bob "Dave Peterson" wrote: 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. Bob wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro keyboard shortcut problem
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. "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit Menu Keyboard Shortcut Problem | Excel Discussion (Misc queries) | |||
keyboard shortcut for a macro | Excel Worksheet Functions | |||
Unassign Macro Keyboard Shortcut? | Excel Programming | |||
Excel 2007 Date Keyboard Shortcut problem | Excel Discussion (Misc queries) | |||
Keyboard Shortcut in Macro | Excel Discussion (Misc queries) |