How to temporarily hide a form?

How do I temporarily hide a form?

I am unable to do Formname.Visible = True


I know I can do Formname.hide but I don't want to run the initialize functions when I do Formname.show...

help !!

well that doesn't really help me. i only have 1 form in my project.
i am having trouble with printpreview. i can't have a form and the print preview window open at the same time. so i am trying to hide the form while it's on the print preview screen and then put it back when print preview is closed.


any thoughts?

Hello Msangapu,

To solve the problem you need to do 2 things. Set the UserForm Modal property to False and be able to minimize and restore the Form like a Window. This set of macros allows you to add the minimize and restore buttons to the Title Bar, and add an icon from a file. The code is long (typical for API calls), but the macros are easy to use.

'///////////////////////////////////////'
'/                                     /'
'/  API calls to add the Minimize and  /'
'/  Restore buttons, and add/change    /'
'/  the icon on the Title Bar of a     /
'/  VBA UserForm. Works with Windows   /'
'/  '95, '98, 2000, and Xp             /'
'/                                     /'
'///////////////////////////////////////'
'
'Written: Jan. 30, 2007
'Author: Leith Ross

'Returns an Icon from a File (.ico)
 Private Declare Function LoadImage _
  Lib "user32.dll" _
   Alias "LoadImageA" _
    (ByVal hInst As Long, _
     ByVal lpsz As String, _
     ByVal uType As Long, _
     ByVal cxDesired As Long, _
     ByVal cyDesired As Long, _
     ByVal fuLoad As Long) As Long

'Direct System what to do with the Window
 Private Declare Function SendMessage _
  Lib "user32.dll" _
   Alias "SendMessageA" _
    (ByVal hWnd As Long, _
     ByVal wMsg As Long, _
     ByVal wParam As Long, _
     lParam As Long) As Long

'Constants for SendMessage
 Const WM_GETICON As Long = &H7F
 Const WM_SETICON As Long = &H80
 Const ICON_SMALL As Long = &H0
 Const ICON_BIG As Long = &H1

'Constants for Load Image's fuLoad Parameter (Load Resource)
 Const LR_DEFAULTCOLOR As Long = &H0
 Const LR_MONOCHROME As Long = &H1
 Const LR_COLOR As Long = &H2
 Const LR_COPYRETURNORG As Long = &H4
 Const LR_COPYDELETEORG As Long = &H8
 Const LR_LOADFROMFILE As Long = &H10
 Const LR_LOADTRANSPARENT As Long = &H20
 Const LR_DEFAULTSIZE As Long = &H40
 Const LR_VGACOLOR As Long = &H80
 Const LR_LOADMAP3DCOLORS As Long = &H1000
 Const LR_CREATEDIBSECTION As Long = &H2000
 Const LR_COPYFROMRESOURCE As Long = &H4000
 Const LR_SHARED As Long = &H8000

'Constants for Load Image's uType Parameter
 Const IMAGE_BITMAP As Long = &H0
 Const IMAGE_ICON As Long = &H1
 Const IMAGE_CURSOR As Long = &H2

'Constants for ShowWindow (nCmdShow)
 Const SW_HIDDEN As Long = 0
 Const SW_NORMAL As Long = 1
 Const SW_MINIMIZED As Long = 2
 Const SW_MAXIMIZED As Long = 3
 Const SW_NOTACTIVE As Long = 4
 Const SW_UNHIDDEN As Long = 5
 Const SW_MINWITHFOCUS As Long = 6
 Const SW_MINNOTACTIVE As Long = 7
 Const SW_RESTORE As Long = 9

'Constants for GetWindow
 Const GW_HWNDFIRST As Long = &H0
 Const GW_HWNDLAST As Long = &H1
 Const GW_HWNDNEXT As Long = &H2
 Const GW_HWNDPREV As Long = &H3
 Const GW_OWNER As Long = &H4
 Const GW_CHILD As Long = &H5

'Window Style constants
 Const WS_DISABLE As Long = 0
 Const WS_MAXIMIZEBOX As Long = &H10000
 Const WS_MINIMIZEBOX As Long = &H20000
 Const WS_THICKFRAME As Long = &H40000    'Style to add a sizable frame
 Const WS_SYSMENU As Long = &H80000
 Const WS_ENABLE As Long = &HFFFFFFFF
 
'Get Window Long constants
 Const GWL_HINSTANCE As Long = (-6)
 Const GWL_HWNDPARENT As Long = (-8)
 Const GWL_ID As Long = (-12)
 Const GWL_STYLE As Long = (-16)
 Const GWL_EXSTYLE As Long = (-20)

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

'Function to Change how Window is Displayed
 Private Declare Function ShowWindow _
  Lib "user32.dll" _
   (ByVal hWnd As Long, _
    ByVal nCmdShow As Long) As Long

'Returns the Window Handle of the Active Window
 Public Declare Function GetActiveWindow _
  Lib "user32.dll" () As Long

'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
  Lib "user32.dll" _
   (ByVal hWnd As Long) As Long

Public Sub MinimizeWindow(Optional ByVal Window_Handle As Long, Optional ByVal With_Focus As Boolean)
  
 Dim RetVal
 
  If With_Focus = True Then
    RetVal = ShowWindow(Window_Handle, SW_MINWITHFOCUS)
  Else
    RetVal = ShowWindow(Window_Handle, SW_MINNOTACTIVE)
  End If
  
End Sub

Public Sub RestoreWindow(Optional ByVal Window_Handle As Long)
 
 Dim RetVal
  
  RetVal = ShowWindow(Window_Handle, SW_NORMAL)

End Sub


Public Sub AddMinBox(Optional Window_Handle As Long)

 Dim hWnd As Long
 Dim BitMask As Long
 Dim WindowStyle As Long

   If Window_Handle = 0 Then
      hWnd = GetActiveWindow()
   Else
      hWnd = Window_Handle
   End If
  
   WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
   BitMask = WindowStyle Or WS_MINIMIZEBOX
  
   Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
   Call DrawMenuBar(hWnd)

End Sub

Public Sub AddMaxBox(Optional Window_Handle As Long)

 Dim hWnd As Long
 Dim BitMask As Long
 Dim WindowStyle As Long

   If Window_Handle = 0 Then
      hWnd = GetActiveWindow()
   Else
      hWnd = Window_Handle
   End If
  
   WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
   BitMask = WindowStyle Or WS_MAXIMIZEBOX

   Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
   Call DrawMenuBar(hWnd)

End Sub
     
Public Function ChangeIcon(ByVal Icon_File_Path As String, Optional ByVal Window_Handle As Long)

  Dim hWnd As Long
  Dim hIcon As Long
  Dim LoadMask As Long

    If Window_Handle = 0 Then
       hWnd = GetActiveWindow()
    Else
       hWnd = Window_Handle
    End If
    
     LoadMask = LR_LOADFROMFILE Or LR_DEFAULTSIZE Or LR_SHARED
     hIcon = LoadImage(0&, Icon_File_Path, IMAGE_ICON, 32, 32, LoadMask)

     Call SendMessage(hWnd, WM_SETICON, ICON_BIG, ByVal hIcon)
     Call DrawMenuBar(hWnd)

End Function

To Install the Macro:
1. Copy the Macro code above using CTRL+C
2. Open Excel and Right Click on any Sheet Tab
3. Click on View Code in the pop up menu
4. Use ALT+I to activate the VBE Insert Menu
5. Press the letter m to insert a Standard Module
6. Paste the macro code using CTRL+V
7. Save the macro using CTRL+S
8. Close the VBE and return to excel using ALT+Q

Calling the Macros:
Change the name of the UserForm to match your Form's name.

Private Sub UserForm1_Activate()
 ' Make the UserForm Modeless
    UserForm1.Modal = False

 ' Add the boxes to the title bar
    AddMinBox()     'Minimize
    AddMaxBox      'Restore/Maximize
  
  ' You can also use a gif or bmp file
     ChangeIcon "C:\My Pictures\My Icon.ico"
End Sub

Sincerely,
Leith Ross

Originally Posted by davesexcel

From what I understand....
When you hide the userform, the userform is just hidden, not unloaded...
so when you show the userform again, it should not initialize because it was just hidden, what are we missing here??

Are you sure about that? It would make sense that it did work that way, but from my experience, it was affecting several variables. When I ran the print_preview, I did the formname.hide and after print_preview was done, I did formname.show.... but when if I clicked on the print_preview again, it comes up blank...

So I figured .hide and .show were doing something funky.