?
Solved

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

Posted on 2008-11-03
9
Medium Priority
?
1,362 Views
Last Modified: 2013-12-26
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
0
Comment
Question by:gbergsma
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22874271
Greg,

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

Wayne
0
 

Author Comment

by:gbergsma
ID: 22874663
When I do that, the form always stays at the front
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22874924
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:gbergsma
ID: 22875203
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
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 22875338
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 22875665
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 22875670
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
 

Author Closing Comment

by:gbergsma
ID: 31512993
Thanks Wayne. I have reworked my code with your suiggestions to get something acceptable going
0
 

Expert Comment

by:deeboll
ID: 35427733
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question