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?
 
Wayne Taylor (webtubbs)Connect With a Mentor 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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.