Switch between Excel windows with Userform open in one

LeanidTerbant

New Member
Joined
Nov 15, 2010
Messages
16
Hi
I have a UserForm (in Excel 2007 created with VBA) which is primarily going to be used to input data into a workbook. The problem is that the data is located in another workbook, and by default, when the UserForm is open in the first workbook, you're not able to switch between Excel windows. I tried setting the UserForm's ShowModal property to False and it sorta worked; the only issue then is that, while it is then possible to switch between workbooks with the UserForm open, the UserForm stays visible over the top of both, and being quite large, it covers most of the data in the second workbook anyway.

Is there a way to either switch between windows and leave the UserForm behind in the first window, or to have an event run when the activeworkbook changes so that i can shrink my UserForm down to nothing when that happens?

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi there,

Have you tried activating the other workbook on userform_Activate?

Code:
Private Sub UserForm_Activate()
    Workbooks("Book2").Activate
End Sub
 
Upvote 0
It makes Book2 the active workbook, you can see things in it.

If this is not what you wanted then I apologise.
 
Upvote 0
Actually, the reason it did nothing was that I forgot to change ShowModal back to True :whistle:
It still doesn't quite do what I want because what I really want to be able to do is switch between windows with the UserForm open in one of the windows, and not just switch windows once on UserForm activation. Thanks anyway though, you've given me some food for thought :)
 
Upvote 0
Have you thought about using what I posted and adding it to a button?

Click on a button called "show sheet 1" and it shows sheet 1 etc?
 
Upvote 0
Good idea...I'll just modify the UserForm height and width between 1 and full size depending on which window is picked to be active.
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top