VBA Excel - allowing a user to open another workbook when a form is open

Hi,

I have a VBA macro that gets information from the user. Sometimes the user wants to open another Excel workbook to find the information to cut/paste. The problem is that while the userform is open, the second workbook won't open.

If there was an event that indicated the user tried to open another workbook, my VBA macro could launch another copy of Excel. Is there such an event?

Is there another way to do this?

Cheers

Greg
gbergsmaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Greg,

If you set the ShowModal property of your userform to False, the user can interact with Excel as they normally would.

Wayne
0
gbergsmaAuthor Commented:
When I do that, the form always stays at the front
0
Wayne Taylor (webtubbs)Commented:
Well, yes. I wasn't aware you wanted it to be hidden when the second workbook was visible.

How are you opening the second workbook?

Wayne
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gbergsmaAuthor Commented:
Hi Wayne

I am just double-clicking on the file from explorer to open the second file. Is there a general function that runs within Excel that lets me know a workbook has been opened? I was thinking I could execute the Hide function of the form

Cheers

Greg
0
Wayne Taylor (webtubbs)Commented:
Greg,

Unfortunately there is no event which could notify you of the users intention to open a workbook. Also, while a Userform is visible with the ShowModal property set to True, no other workbook can be opened, at least not via the user interface (which includes opening from Explorer).

You would need to have a button on your form to allow your user to select a file and open it, and you can handle the hiding of the form from there. But then you have the problem of being notified when the workbook is closed, which you'd need to re-show your userform.

I still think the best idea is to set the ShowModal property of the form to False. So what if the form stays to front? You can always move it, and you have complete access to any opened workbooks.

Wayne
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EDDYKTCommented:
use userform1.show 0


ie
in module

Public userfrm As UserForm1
Sub Button1_Click()

Set userfrm = New UserForm1
userfrm.Show 0
End Sub

in userform1

0
EDDYKTCommented:
use userform1.show 0


ie
in module

Public userfrm As UserForm1
Sub Button1_Click()

Set userfrm = New UserForm1
userfrm.Show 0
End Sub

in userform1
Private Sub UserForm_Terminate()
Set userfrm = Nothing
End Sub

in workbook
Private Sub Workbook_Activate()
If Not userfrm Is Nothing Then userfrm.Show 0
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If Not userfrm Is Nothing Then userfrm.Hide
End Sub
0
gbergsmaAuthor Commented:
Thanks Wayne. I have reworked my code with your suiggestions to get something acceptable going
0
deebollCommented:
I have a similar problem like this - when I open the userform I created in one xcel file, I am not able to use the other excel files. So, I set my userform's showmodal property to False. I now am able to open other excel files. Although the form stays on the top, it is acceptable but my problem my buttons in the userform are all greyed out and I am not able to do anything when I come back to the userform after finishing my work with the other excel file. Any suggestions?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.