Using a hot key to activate a macro

[EverClear]

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]

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]

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]

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]

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

 

[EverClear]

Great! Thanks again!!

 

[Jonmo1]

Glad to help, thanks for the feedback.