Avatar of Sheldon Livingston
Sheldon Livingston
Flag 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))

Microsoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
Donnaa5dcp

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:
MyWb.Close
Sheldon Livingston

ASKER
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.
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sheldon Livingston

ASKER
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
Next

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

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 . 
Sheldon Livingston

ASKER
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?

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Fabrice Lambert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sheldon Livingston

ASKER
Forgot about modaless!
byundt

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