CODEWITHSUNDEEP
excelvba
oaklodge
oaklodge

Reputation: 748

Setting up procedures in Excel for hotkey use

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.

Upvotes: 1

Views: 78

Answers (2)

Vityata
Vityata

Reputation: 43585

  1. Put the procedure Sub SaveWorksheetsAsCSV() in a module.
  2. Write the word Public in front of it Public Sub SaveWorksheetsAsCSV()
  3. 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

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

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.

Upvotes: 1

Related Questions