In Excel 2019 16.0.10361.20002 32-bit, when I open a file from a userform (which for me is always), the hotkeys on the Ribbon don’t work. Here’s how I reproduce:
In a new workbook, add a userform named UserForm1. Add a commandbutton to the userform. Paste this code in the userform’s code module.
1 2 3 4 5 6 7 |
Private Sub CommandButton1_Click() Application.RecentFiles(1).Open Unload Me End Sub |
Add a standard module to the workbook and past this code into it.
1 2 3 4 5 |
Public Sub OpenRecent() UserForm1.Show End Sub |
Close the VBE and return to Excel. Press Ctrl+F8 and run the macro and click the button. Now press the Alt key. I don’t get these guys:
If I Alt+Tab away and back, they show up again. If you launch the form from the VBE, you have to Alt+Tab to get to Excel so the test doesn’t work.
Also, I hadn’t tried this until just now, but I can’t even click on the Ribbon with mouse. Nothing happens. My next tack is to close and unload the userform before opening the file and see if that takes care of it. It’s odd though. It’s the only odd thing in 2019 and building up a pretty nice rant about it. Two posts in one year? Yep, it could happen.
UserForm1.Show (vbModeless) works. so it seems to be only when the form is open as a modal form
This can be circumvented by first hiding the form:
Private Sub CommandButton1_Click()
Me.Hide
Unload Me
Application.RecentFiles(1).Open
End Sub
Both of those work. I don’t really want the form to be modeless, so that’s not ideal. As for JKP’s, I assumed code execution stopped at Unload Me, but apparently not. It feels a little wrong having that in the middle of a sub, but it seems to work.
It is funny that you joke about possibly having two posts in one year because I have started to think of “daily” dose of Excel as more like a quarterly or semi-annual dose — haha!
Great article!
Is Ribbon.customUIonLoad the last event when opening a workbook?
I’m trying to prevent to possibility of changing the ribbon XML so that the visibility of Admin buttons can be changed.