Run VBA macro from a hotkey

Question

I have this macro that formats my Excel sheet. It changes the color of the top row and adds a filter, freezes pane, etc.

I have a personal workbook I saved it to, so I can use it in any Excel file I open.

It works if I run the macro from the developer tab. If I try running it using a hotkey it does not work.

The only thing that works when I use the hotkey is it will add a filter to the top row.

Sub FormatSheet()
'
' FormatSheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+L

Worksheets(1).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.349986266670736
    .PatternTintAndShade = 0
End With
Selection.AutoFilter
With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Cells.Select
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Cells.EntireColumn.AutoFit

End Sub

Let me see if I understand. You say that the Macro runs, but it runs differently when you use the hot key. Is that correct? — Karlomanio, Dec 31 '19 at 19:46
Correct. I have the macro saved in a module in another workbook though. I have a feeling that is what is causing the problem. I want the macro to work on all excel files I open though not just one workbook. So that is why I was saving it there. — SkysLastChance, Dec 31 '19 at 19:50
If you are using two workbooks, you need to use explicit object references. Your code is currently defaulting to whatever workbook/worksheet is currently active and that is going to give weird results. — Warcupine, Dec 31 '19 at 19:58
This will be helpful as well: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Warcupine, Dec 31 '19 at 19:59

score 3 · Accepted Answer · answered Dec 31 '19 at 20:04

3

did you set the hotkeys? open up your macros (Alt+f8) go to options(for that macro) and set your ctrl+shift+L. also, naming the module removes all hotkey function.

answered Dec 31 '19 at 20:04

jsteurer

244
1
2
6

That is exactly what was happening. I thought it was kind of working too because Ctrl+Shift+L adds a filter. In reality it was never running too funny. Just my luck. Thank you! – SkysLastChance Dec 31 '19 at 20:11

Run VBA macro from a hotkey

1 Answers1