Shortcut code VBA not working

[rob_sheeds]

I have saved this and another sub in my personal workbook, but it wont run when I press Ctrl + Shift + e
Can anyone please help

Public Sub Open_Unprotect_SaveAs()
Application.OnKey "^+e", "Unprotect_SaveAs()"
End Sub

Public Sub Unprotect_SaveAs()
'
' Unprotect and Save as .xls Macro
Dim MyString As String
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
MyString = Range("C4") & " " & Range("C5") & " " & Range("D4")
FileDrive = "R:\EOM GL Reporting\Franchising\P&L's to be uploaded\2012\29082011"
ActiveWorkbook.SaveAs Filename:=MyString, FileFormat:=56
End Sub

 

[repairman615]

Hello,

I see you are using onkey.

Have you tried to do this natively?

Try this:



Alt + F8 to open the macro dialog
Select your macro
then click the Options button

There you can specify a shortcut key (hold shift and press E).

Does that make any difference?

-Jeff

 

[rob_sheeds]

Thanks Jeff, I did that and it works, though it isnt saving in the directory. It is getting the file name correct and then asks me to save over the file, but the file doesnt exist both before or after. I have checked the directory, even copied the directory into window explorer and still nothing?

 

[rob_sheeds]

Ok worked it out, and an issue with password. Thanks again jeff.

Public Sub Unprotect_SaveAs()
'
' Unprotect and Save as .xls Macro
Dim MyString As String
Worksheets("Data Input").Activate
ActiveSheet.Unprotect Password:="sg6228"
ActiveWorkbook.Unprotect Password:="sg6228"
Worksheets("Data Input").Activate
MyString = Range("C4") & " " & Range("C5") & " " & Range("D4")
FileDrive = "R:\EOM GL Reporting\Franchising\P&L's to be uploaded\2012\29082011\"
ActiveWorkbook.SaveAs Filename:=FileDrive & MyString, FileFormat:=56
End Sub

 

[repairman615]

Your welcome, glad to help!