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:
Has anyone dealt with this before?
What would you suggest?
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?