Unresponsive ribbon after opening new workbook Excel 2016 VBA

Stavros jonjon

New Member
Joined
Jun 13, 2018
Messages
1
I'm quite a rookie when it comes to programming, however I have been able to build a couple of useful applications in excel, which automate some of my everyday tasks.
Currently I am having an annoying issue when trying to open a new workbook using <code>workbooks.open()</code> in Excel 2016.
I have made a workbook with a button which calls a userform. Once the form is launched, the user can select to download and open several types of .xls files. When the user clicks the form's OK button all the appropriate functions are called, the selected workbooks are opened and the form gets unloaded and hidden.
However, the ribbon of the last workbook that was opened, which btw is the one that is currently active, is unresponsive and the only way to overcome this, is to ALT+TAB between open windows.
It seems like the "focus" is still on the initial workbook with the button, because if I call a <code>Msgbox</code> after the form is unloaded, that's where it appears. It's worth mentioning that this happens although the initial workbook is not the one that's active!
After doing some experimenting I was able to solve the issue by disabling <code>Application.ScreenUpdating</code> when my function is called and then re-enabling it just before the form is unloaded.
This however only works when multiple workbooks are being opened at the same time. If the user chooses to open only one workbook then the problem persists. I came across a suggestion to make the userform modeless, which indeed solves the issue but creates other kinds of unwanted behavior.
A simplified version of the code which replicates the problem is as follows:


Code:
<code>Private Sub CommandButton1_Click()
Application.ScreenUpdating = False 'solves the issue but only for multiple files
If OptionButton1 Then
    Workbooks.Open ("http://www.admie.gr/fileadmin/user_upload/reports/DayAheadSchedulingUnitAvailabilities/20180602_DayAheadSchedulingUnitAvailabilities_01.xls")
Else
    Workbooks.Open ("http://www.admie.gr/fileadmin/user_upload/reports/DayAheadSchedulingUnitAvailabilities/20180603_DayAheadSchedulingUnitAvailabilities_01.xls")
    Workbooks.Open ("http://www.admie.gr/fileadmin/user_upload/reports/DayAheadSchedulingUnitAvailabilities/20180604_DayAheadSchedulingUnitAvailabilities_01.xls")
End If
Application.ScreenUpdating = True 'solves the issue but only for multiple files
Unload UserForm1
UserForm1.Hide
MsgBox ActiveWorkbook.Name 'the msgbox will be displayed on the workbook which called the userform, although it's not the active one
End Sub

Sub Button1_Click() 'calls the userform
UserForm1.OptionButton1.Value = True
Load UserForm1
UserForm1.Show vbModeless 'solves the issue but creates unwanted behavior
End Sub</code>


Has anyone dealt with this before?
What would you suggest?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top