minimized userform pops up when new spreadsheet opened

[garydp]

I have a userform that I have added a minimize button to. when another spreadsheet is opened the userform pops back up over the top of it. is there anyway to keep the form minimized until it is clicked on to maximize it?

[Leith Ross]

Hello garydp,

It would help to see what code you used to minimize the UserForm with.

[garydp]

I used the code from here

http://stackoverflow.com/questions/6...for-a-userform

[Aussiebear]

Did you set the code to work with a particular workbooks?

[Leith Ross]

Hello garydp,

I assume you used th API code. Not sure what Ivan did to make the UserForm stay on top. I wrote this awhile back to add the minimize/maxize and restore buttons to a UserForm. Copy all of the code into a Standard VBA module and then add the additional code to yyour UserForm's Activate event.

API Code to Add the Buttons:

' Written: October 07, 2007
' Author:  Leith Ross
' Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX    As Long = &H20000
Public Const MAX_BOX    As Long = &H10000
Const SC_CLOSE      As Long = &HF060
Const SC_MAXIMIZE   As Long = &HF030
Const SC_MINIMIZE   As Long = &HF020
Const SC_RESTORE    As Long = &HF120

Private Declare Function SetWindowPos _
    Lib "user32.dll" _
        (ByVal hWnd As Long, _
         ByVal hWndInsertAfter As Long, _
         ByVal X As Long, _
         ByVal Y As Long, _
         ByVal cx As Long, _
         ByVal cy As Long, _
         ByVal wFlags As Long) _
    As Long

Private Declare Function GetWindowLong _
    Lib "user32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long) _
    As Long
               
 Private Declare Function SetWindowLong _
    Lib "user32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long, _
         ByVal dwNewLong As Long) _
    As Long
     
'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
    Lib "user32.dll" _
        (ByVal hWnd As Long) _
    As Long

'Returns the Window Handle of the Window accepting input
 Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)
Dim BitMask As Long
    Dim Window_Handle As Long
    Dim WindowStyle As Long
    Dim Ret As Long
If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
   Window_Handle = GetForegroundWindow()
   WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
   BitMask = WindowStyle Or Box_Type
   Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
   Ret = DrawMenuBar(Window_Handle)
End If
End Sub

UserForm Code Needed:

Private Sub UserForm_Activate()
    Call AddToForm(MIN_BOX)
    Call AddToForm(MAX_BOX)
End Sub

[garydp]

i have the same issue with this code. When the spreadsheet is opened it opens the userform and makes the spreadsheet invisible. I then minimize the userform and open a different spreadsheet and the userform comes back on to the screen. then when the second opened spreadsheet is close the original invisible one is now visible.

[Leith Ross]

Hello garydp,

In that case, we can rule out the code to minimize the UserForm as the problem. There is something else happening in your workbook or it's code that is blame.

Could you post a copy of the workbook for review?

[garydp]

All I have done is create a new .xlsm file added a user form and a module added your code to the user form and module and then ran it. selected minimize and then tried opening another spread sheet and the user form pops back up. I don't mean a brand new spread sheet in a new window just opened one that was already created.

[GTO]

Hi there,

Could you attach the workbook, or if sensitive info included, dummy-up one with the same code and problem, and attach that? It might help us see what is happening.

Mark

[garydp]

As stated previously I created a new workbook added the code that leith posted and ran that on its own. Selected minimise on the user form and opened another workbook. When that workbook opens the user form maximises without even touching it