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?
 

Forum statistics

Threads
1,082,044
Messages
5,362,847
Members
400,694
Latest member
Ave663

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top