excel data form

[RuthandAndy]

I would like to have a data entry user-form remain open but in the background when using related excel workbooks, and then be able to return to the form retaining its data. Is there a way to do this?

 

[Leith Ross]

Hello RuthandAndy,

Are you referring to a VBA UserForm or something else?

In the background meaning like another window you can minimize and restore?

If the answer to both questions is yes then the UserForm can be modified to behave this way.

 

[RuthandAndy]

Yes, it is a VBA user form,
and yes, that is exactly what we want, like a window that can be minimized and restored.
How is it done?

 

[Leith Ross]

Hello RuthandAndy,

This macro will allow you to add the minimize and restore buttons to your UserForm.

'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

Example of Callling the Macro from the UserForm

Private Sub UserForm_Activate()
  AddToForm MIN_BOX
  AddToForm MAX_BOX
Ens Sub

NOTE: You must set the UserForm property "ShowModal" to False before running this code for it to work correctly. If you do not change this property the you won't be able to access the worksheets until the UserForm is closed.

 

[RuthandAndy]

Thank you for the Max/Min/Restore button macro. The only problem I had was the Public Const declarations for MIN_BOX and MAX_BOX were not allowed so I removed the Public. This form is called from ThisWorkbook as well as Module1 so, do you know how I can get them both so see the constant variables?


OOPS!

I just tried the Macro again with the public constants and it worked fine.
Don't know what the problem was before.

Thanks for your help

 

[Leith Ross]

Hello RuthandAndy,

Glad that it is working for you. It is not uncommon in VBA for a macro to run a few times then suddenly, for no apparent reason, fail. I have found that frequently saving my project code reduces this problem greatly.

 

[idris_cakir]

Hello,

thanks for the nice solution. It work's fine.

I have a extended question. Can we set as default that the userform open in maximized.
Can I avtivate the "MAX_BOX" automaticlly.