Setting up procedures in Excel for hotkey use

Question

I want the procedure below embedded in a macro-enabled Excel (xlsm) file.

Sub SaveWorksheetsAsCSV()
Dim WS As Excel.Worksheet
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
    WS.Copy
    ActiveWorkbook.SaveAs Filename:=CurrentWorkbook & "-" & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
Next
Application.DisplayAlerts = True
End Sub

I want the procedure to be assigned to a hotkey (Shift+Alt+S), so I also embed this procedure.

Private Sub Workbook_Open()
Application.OnKey "+%s", "SaveWorksheetsAsCSV"
End Sub

I put both of these procedures in the “ThisWorkbook” of the “Microsoft Excel Objects” folder, because I want the hotkey’ed save as CSV procedure to be usable from any sheet after the file is opened.

I can bring up the macro list with Alt+F8 and run ThisWorkbook.SaveWorksheetsAsCSV and all sheets are saved as CSV files as expected. But when I use the hotkey (Shift+Alt+S), I get the error message “Cannot run macro...” I'm fairly certain it’s not a permission thing, for testing I’ve turn on “Enable all macros” in Macro Security settings.

Can someone help me set this up properly? This XLSM file will be used as a template that people will copy and modify and then convert to CSV for import into another tool.

Vityata · Accepted Answer · 2018-02-28T12:35:35.213

2

Put the procedure Sub SaveWorksheetsAsCSV() in a module.
Write the word Public in front of it Public Sub SaveWorksheetsAsCSV()
Make sure that you write it only once.

Then it will work from any worksheet, independently. This is quite ok, let it stay:

Private Sub Workbook_Open()
    Application.OnKey "+%s", "SaveWorksheetsAsCSV"
End Sub

Depending on the way how you (user users) use Excel, it might be a good idea to write a similar Workbook_Close() procedure:

Private Sub Workbook_Close()
    Application.OnKey "+%s"
End Sub

edited Feb 28 '18 at 12:35

answered Feb 28 '18 at 11:21

Vityata

42,633
8
55
100

1

Writing a `Workbook_Close()` Routine, it seems to be preferable to omit the second `OnKey` parameter completely to revert the key to its normal result - see https://stackoverflow.com/questions/48552588/application-onkey-fails-to-reset-properly – T.M. Feb 28 '18 at 12:34
1

@T.M. - true. And if you take a look here - https://github.com/Vitosh/VBA_personal/search?utf8=%E2%9C%93&q=onkey&type=, you would see how much I have learned between March and June 2017 :D – Vityata Feb 28 '18 at 12:37

score 1 · Answer 2 · answered Feb 28 '18 at 11:34

Set the Application.MacroOptions
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-macrooptions-method-excel

Public Sub SetMacroOptions()

    Application.MacroOptions _
        Macro:="SaveWorksheetsAsCSV", _
        Description:="Saves the sheet as a CSV", _
        HasShortCutKey:=True, _
        ShortcutKey:="s"

End Sub

Not sure if you can set it with Alt using this method though.

Setting up procedures in Excel for hotkey use

2 Answers2