Using a hot key to activate a macro

EverClear

New Member
Joined
Oct 23, 2012
Messages
32
Hi there!!

I've built a macro to open a file and copy one of its worksheets into a different file (both files are in Excel 97-2003 format). The macro works great when I manually select the macro from the macros dialog box and click "Run." However, I tried to assign the macro to a hotkey. Now when I click the hotkey (Ctrl+Shift+whatever...), all the macro will do is open the file - it does not copy the worksheet into the different file. I *don't* receive an error message. The macro just opens the file, and then ends.</SPAN>

Here’s my code so far:</SPAN>

Sub Get_MidMonthFile()</SPAN>

Dim StrFile As String</SPAN>
Set wbSection = ActiveWorkbook</SPAN>
Set wsSection = wbSection.ActiveSheet</SPAN>

xdir = wbSection.Path</SPAN>

Application.DisplayAlerts = False</SPAN>
On Error GoTo 0</SPAN>
Application.DisplayAlerts = True</SPAN>

StrFile = xdir + "\" + "Monthly File.xls"</SPAN>

If Dir(StrFile) = "" Then</SPAN>
MsgBox "No File Found: Monthly File.xls"</SPAN>
Else</SPAN>
Workbooks.Open Filename:=StrFile</SPAN>
End If</SPAN>

wbSection.Activate </SPAN>‘<------</SPAN></SPAN>Using the hotkey, the code macro stops here. Without the hotkey, the macro continues to run to completion.</SPAN>

Windows("Monthly File.xls"). _</SPAN>
Activate</SPAN>
Sheets("monthly sheet").Select</SPAN>
Sheets("monthly sheet").Copy Before:=Workbooks( _</SPAN>
"testing.xls").Sheets(2)</SPAN></SPAN>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Don't use the SHIFT key in the Hotkey setup.
I'm not sure why, but it thinks you're holding down the Shift Key while opening a book.
And that disabled macros.
 

EverClear

New Member
Joined
Oct 23, 2012
Messages
32
Thanks for the info!! The trouble is - in the dialog box where I have the option to assign the hotkey, it *automatically* inserts the "Shift." For example, if I want the hotkey to be "Crtl+J," when I try to enter the "J" in the dialog box, Excel/vba/etc. automatcially changes it to "Ctrl+Shift+J."
 

EverClear

New Member
Joined
Oct 23, 2012
Messages
32
Aha!!! NOW I know what you mean! If I use an uppercase letter to assign my hotkey, then Excel will automatically insert the "Shift" (i.e. Ctrl+SHIFT+J). However, if I use a LOWER case letter (i.e. Ctrl+j), then I'm fine and the macro works great.

Thanks for the point in the right direction!! :)



Thanks for the info!! The trouble is - in the dialog box where I have the option to assign the hotkey, it *automatically* inserts the "Shift." For example, if I want the hotkey to be "Crtl+J," when I try to enter the "J" in the dialog box, Excel/vba/etc. automatcially changes it to "Ctrl+Shift+J."
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Don't use J
use j

See the difference?
You have to press SHIFT (or have the CAPS Lock on) to get the J, so it assumes you want to use CTRL+SHIFT+J
 

Forum statistics

Threads
1,082,478
Messages
5,365,789
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top