Show Userform Behind Active Windows

[gsurge]

Hello,

I currently have a userform that always remains on top of other excel workbooks (unless opened in another instance of excel). How can I make the userform move to the background when another workbook is activated, and automatically come back on top when the sheet with the userform is selected again?

Thanks
surge

[Leith Ross]

Hello surge,

Set the UserForm.ShowModal property to False.

[gsurge]

The setting is marked as false, but the userform still remains above other workbooks

I made this change in the properties window on the left side of the object editor...does that make a difference?

[Leith Ross]

Hello gsurge,

I believe I have misunderstood you. When you say workbook, are you referring to the Excel file that is open or a worksheet in that file?

[gsurge]

Any other workbook or even worksheet that is open (in the same instance of excel) will always appear behind the open userform box...

[Leith Ross]

Hello gsurge,

Thanks, I understand the problem now. I can provide you with code to make the UserForm behave like a window: Minimize, Maximize, Restore. Would that be an option of interest?

[gsurge]

yes i would appreciate that. thank you

[Leith Ross]

Hello gsurge,

Here is the code. Copy this into a separate VBA module in your workbook. You will need to call the macro twice from the UserForm. There is an example below the macro code.

Please Login or Register  to view this content.

Example of Using the Macro on a UserForm
Copy this code to the UserForm.

Please Login or Register  to view this content.

[gsurge]

This is perfect! Thanks so much

[Leith Ross]

Hello gsurge,

You're welcome. Glad you didn't have any problems installing or running the code.

[samirsingh]

Hi Leith,

I tried to use the code that you provided above. But, it doesn't seem to work for me. I am using excel 2007.

i have coded this code in activate event of my userform abd coped the code in one Module. Acticate event is called when i do a show userform and code executed successfully. But the buttons are not added. Could you please help me here.

Private Sub UserForm_Activate()
AddToForm MIN_BOX
AddToForm MAX_BOX
End Sub

[samirsingh]

Hi Leith,

Somehow this seems to be working now. I am able to minimize my userform and open other excel sheets. I have changed the userform to vbmodless. Thanks for the code !! However, i have a question here : If i open another excel worksheet when the userform is minimized, the userform gets maximized by itself. Then again i have to minimize it. Is there a way to handle it ?

Hi Leith,

I tried to use the code that you provided above. But, it doesn't seem to work for me. I am using excel 2007.

i have coded this code in activate event of my userform abd coped the code in one Module. Acticate event is called when i do a show userform and code executed successfully. But the buttons are not added. Could you please help me here.

Private Sub UserForm_Activate()
AddToForm MIN_BOX
AddToForm MAX_BOX
End Sub

[alvaroramos]

All declares in my excel module is in error, because all are with font red as this one
Private Declare Function SetWindowLong _
Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

[radman16]

You must be running a 64 bit version. Add SafePtr between Declare and Function then change Long to LongPtr

[FDibbins]

All declares in my excel module is in error, because all are with font red as this one
Private Declare Function SetWindowLong _
Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread