Link to home
Start Free TrialLog in
Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America

asked on

Open a workbook in Excel VBA

I can open a workbook using Excel VBA but cannot close it manually... it's as if the VBA code has a grip on it.

How can I open the workbook in such a fashion that a user can close it at will?

Workbooks.Open (wksHidden.Range("A1").Offset(intCounter + 1, 1))

Avatar of Donnaa5dcp
Flag of United Kingdom of Great Britain and Northern Ireland image

that's an issue within Excel 2016. It is obvious that the ActiveWorkbook object variable is not updated or set correctly by Excel. So I suggest to use a global variable for the workbook instead.

Add this line at the top of the code module:
Dim MyWb As Workbook 
Then use this line to open a file:
Set MyWb = Workbooks.Open("C:\Users\User Name\Documents\Test2")
And this line to close it:
Avatar of Sheldon Livingston


I don't want Workbook A to close Workbook B... just open it.

But I find that I cannot close Workbook B until I stop code execution in Workbook A.

I'm looking for WB A to open WB B and then forget about it.
Avatar of byundt
Do I understand you correctly: you want the user to be able to close Workbook B manually while in the middle of running a macro? If so, you need to return control to the user by terminating the macro.
This is my latest attempt at doing just that:

Private Sub cmdOpen_Click()

If lstWorkbooks.ListIndex = -1 Then Exit Sub

Dim intCounter As Integer

For intCounter = 0 To lstWorkbooks.listCount - 1
  If lstWorkbooks.Selected(intCounter) Then
    Exit For
  End If

Workbooks.Open (wksHidden.Range("A1").Offset(intCounter + 1, 1))

End Sub

Open in new window

You can't do anything with WB B... for example, clicking File does nothing... no menu pops up, etc.Cannot type in the sheet or anything.
With a few exceptions, the user can't do anything while VBA code is running because VBA has total control.

One type of exception occurs when VBA displays a dialog requiring a response from the user. In that case, the user may respond to the dialog.

Another exception occurs when a userform has been displayed with its ShowModal property set to FALSE in its VBA Properties window, or by using a statement like: UserForm1.Show False . 
So how would one provide a list of workbooks, in a listbox on a form, that a user could highlight and click an open button?

Are you thinking that since the form is still open, the code is still running and thus there isn't a way?

User generated image
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forgot about modaless!
As described in my previous Comment, you can display a userform in "modeless" state by setting its .ShowModal property to False in the Properties window in the VBA Editor. You can also display the userform in "modeless" state by using the  optional parameter False when showing it (e.g. Userform1.Show False).