Keyboard shortcut not working for a macro

[rizzo93]

I'm having trouble getting a macro to run when I press a key combination.


This code sets the worksheets to VeryHidden:

Sub DeliveryPrep()
    Worksheets("Leader Info").Visible = xlVeryHidden
    Worksheets("Calculations").Visible = xlVeryHidden
    Worksheets("Details").Visible = xlVeryHidden
    Worksheets("Dynamic Records").Visible = xlVeryHidden
    Worksheets("Email").Visible = xlVeryHidden
    Worksheets("GhostData").Visible = xlVeryHidden
    Worksheets("PivotData").Visible = xlVeryHidden
End Sub

I then went into the Macro dialog (not VBA) to assign a shortcut: Ctrl+Shift+O. This works.


But I also created the reverse of the code above to make those pages visible:

Sub MakeSheetsVisible()
    Worksheets("Leader Info").Visible = xlVisible
    Worksheets("Calculations").Visible = xlVisible
    Worksheets("Details").Visible = xlVisible
    Worksheets("Dynamic Records").Visible = xlVisible
    Worksheets("Email").Visible = xlVisible
    Worksheets("GhostData").Visible = xlVisible
    Worksheets("PivotData").Visible = xlVisible
End Sub

The shortcut for this one is Ctrl+Shift+L. This does NOT work. Instead, I get an error message:

Run-time error '1004':
Unable to set the Visible property of the Worksheet class


What am I missing here please?

 

[RoryA]

Can you manually run the MakeSheetsVisible macro without that error occurring?

 

[jmacleary]

<code>Try repacing xVisible with </code><code class="vb plain">xlSheetVisible</code>

 

[rizzo93]

Thanks jmacleary and RoryA. jmacleary nailed it.

But but now I realized that I need to make it private which, at least from my testing, doesn't make them respond to the keyboard shortcuts.


I've looked at KeyDown and OnKey, but it's still a bit confusing.


I don't want to remap any keys, just have the macros execute when I press Ctrl+Shift+O and Ctrl+Shift+L.


Do you have any suggestions?