5 thoughts on “Opening a File from a Userform Disables Ribbon”

1. UserForm1.Show (vbModeless) works. so it seems to be only when the form is open as a modal form

2. This can be circumvented by first hiding the form:

   Private Sub CommandButton1_Click()
   Me.Hide
   Unload Me
   Application.RecentFiles(1).Open
   End Sub

3. 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.

   Visual Basic

   Private Sub cmdOpen_Click() Dim wb As Workbook If Me.lbxFiles.ListIndex > -1 Then On Error Resume Next Set wb = Workbooks(Me.lbxFiles.Column(1)) On Error GoTo 0 If wb Is Nothing Then On Error Resume Next 'Application.DisplayAlerts = False Me.Hide Unload Me Set wb = Workbooks.Open(Me.lbxFiles.Value, False, , , , , , , , , , , True) 'Application.DisplayAlerts = True On Error GoTo 0 If wb Is Nothing Then RemoveRecent Me.lbxFiles.Value If Left$(Me.lbxFiles.Value, 2) <> "\\" Then ChDrive Left$(Me.lbxFiles.Value, 3) End If ChDir PathOnly(Me.lbxFiles.Value) Application.Dialogs(xlDialogOpen).Show Else wb.RunAutoMacros xlAutoOpen End If Else wb.Activate End If Else SetCurrentDirectory Me.lbxPlaces.Value Application.Dialogs(xlDialogOpen).Show End If Unload Me End Sub

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

   Private Sub cmdOpen_Click()          Dim wb As Workbook          If Me.lbxFiles.ListIndex > -1 Then         On Error Resume Next             Set wb = Workbooks(Me.lbxFiles.Column(1))         On Error GoTo 0                  If wb Is Nothing Then             On Error Resume Next             'Application.DisplayAlerts = False                 Me.Hide                 Unload Me                 Set wb = Workbooks.Open(Me.lbxFiles.Value, False, , , , , , , , , , , True)             'Application.DisplayAlerts = True             On Error GoTo 0                          If wb Is Nothing Then                 RemoveRecent Me.lbxFiles.Value                 If Left$(Me.lbxFiles.Value, 2) <> "\\" Then                     ChDrive Left$(Me.lbxFiles.Value, 3)                 End If                 ChDir PathOnly(Me.lbxFiles.Value)                 Application.Dialogs(xlDialogOpen).Show             Else                 wb.RunAutoMacros xlAutoOpen             End If         Else             wb.Activate         End If     Else         SetCurrentDirectory Me.lbxPlaces.Value         Application.Dialogs(xlDialogOpen).Show     End If                Unload Me      End Sub

4. 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!

5. 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.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.