Switch between Excel windows with Userform open in one

[LeanidTerbant]

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

 

[JamesW]

Hi there,

Have you tried activating the other workbook on userform_Activate?

Private Sub UserForm_Activate()
    Workbooks("Book2").Activate
End Sub

 

[LeanidTerbant]

That doesn't really seem to do anything

 

[JamesW]

It makes Book2 the active workbook, you can see things in it.

If this is not what you wanted then I apologise.

 

[LeanidTerbant]

Actually, the reason it did nothing was that I forgot to change ShowModal back to True
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

 

[JamesW]

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?

 

[LeanidTerbant]

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