PDA

View Full Version : minimized userform pops up when new spreadsheet opened



garydp
11-03-2015, 02:13 AM
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
11-03-2015, 02:32 PM
Hello garydp,

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

garydp
11-03-2015, 02:56 PM
I used the code from here

http://stackoverflow.com/questions/6567259/how-to-add-minimize-button-for-a-userform

Aussiebear
11-03-2015, 03:37 PM
Did you set the code to work with a particular workbooks?

Leith Ross
11-03-2015, 04:13 PM
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
11-04-2015, 04:53 AM
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
11-04-2015, 01:00 PM
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
01-24-2016, 09:21 AM
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
01-26-2016, 12:27 AM
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
01-26-2016, 01:44 AM
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