Results 1 to 5 of 5

Thread: Show Form Only

  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Smile Show Form Only

    Hi Pros,

    When a user opens an Excel Application I would like it to show just the form, not anything else.

    I am using the following code in This Workbook:

    Private Sub Workbook_Open()
    Application.Visible = False
    UserForm1.Show
    End Sub


    This code by itself can create problems accessing the application again.

    To prevent that I've read that a command button with the following code needs to be added:

    Private Sub CommandButton1_Click()
    Application.Visible = True
    End Sub


    However, doing this way gives the user what the developer is attempting to do in the first place, not showing anything but the form.

    Another problem occurs when the user closes the form, the dialogue box Save, Don't Save, Cancel appears.

    I would like the user to take a quiz and close the form without saving anything.

    Any ideas on how to accomplish this would be appreciated.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excel,

    One way is to have a double click event make an invisible textbox visible on the form. The user then enters a password to run the

    Application.Visible = True


    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-03-18)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    If you place this code in a standard module:
    Code:
    Option Explicit
    
    Sub Auto_Open()
    
       Application.Visible = False
       UserForm1.Show
    
    End Sub   'Auto_Open
    Then create a button on your form called cmdClose and place this code in the Form's module:
    Code:
    Option Explicit
    
    Dim bCloseMode As Boolean
    
    Private Sub UserForm_Initialize()
    
       bCloseMode = False
       
    End Sub   'UserForm_Initialize()
    
    Private Sub cmdClose_Click()
    
       Application.Visible = True
       bCloseMode = True
       Unload Me
       
    End Sub   'cmdClose_Click
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
          If bCloseMode Then
            Cancel = False
          Else
             MsgBox "Please use the Red Close to Exit the Dialog", _
                    vbOKOnly + vbExclamation, _
                    "Warning: Improper Attempt to Exit Dialog"
             Cancel = True
          End If
             
    End Sub   'UserForm_QueryClose
    It will do what I think you are asking. The thing to know here is that you have to capture the close box (X) on the form and process it otherwise the user will close both the form and application by clicking the Close Box!

    Test File: EN-HideShowApplication.xlsm

    This takes care of the form problem. However, It you want to insure the user doesn't SAVE anything other code will be needed but the exact code depends on if you want the user to return to the application first or exit directly from the form. Post back and let us know.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-03-18)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excel,

    If you would like to consider my approach, consider adding this code to yours. Your Userform will open without the Close Button (red "X") in the right upper corner using a Windows API. If you double click the form, a hidden textbox will appear. Using the Textbox1_KeyUp event, entering a correct password will immediately make the Excel GUI visible. Change the password "Password" to what you want and no matter which method you use, your form must have some method to allow the user to close the form and exit the program

    HTH,
    Maud

    In the UserForm Module:
    Code:
    '------------------------------
    'FIND THE USERFORM WINDOW
    Private Declare Function FindWindow Lib "user32" _
            Alias "FindWindowA" ( _
            ByVal lpClassName As String, _
            ByVal lpWindowName As String) As Long
            
    '------------------------------
    'GET THE CURRENT WINDOW STYLE
    Private Declare Function GetWindowLong Lib "user32" _
            Alias "GetWindowLongA" ( _
            ByVal hWnd As Long, _
            ByVal nIndex As Long) As Long
    
    '------------------------------
    'SET THE NEW WINDOW STYLE
    Private Declare Function SetWindowLong Lib "user32" _
            Alias "SetWindowLongA" ( _
            ByVal hWnd As Long, _
            ByVal nIndex As Long, _
            ByVal dwNewLong As Long) As Long
    
    Const GWL_STYLE = -16
    Const WS_SYSMENU = &H80000
    
    
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    '------------------------------
    'MONITOR FOR THE CORRECT PASSWORD WITH EACH CHARACTER ENTERED
    If TextBox1.Value = "Password" Then Application.Visible = True
    End Sub
    
    
    Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    '------------------------------
    'TEXTBOX VISIBLE ON DOUBLE CLICK
    TextBox1.Visible = True
    End Sub
    
    
    Private Sub UserForm_Initialize()
       Dim hWnd As Long, lStyle As Long
    '------------------------------
    'WHICH TYPE OF USERFORM
       If Val(Application.Version) >= 9 Then
          hWnd = FindWindow("ThunderDFrame", Me.Caption)
       Else
          hWnd = FindWindow("ThunderXFrame", Me.Caption)
       End If
    '------------------------------
    'GET THE CURRENT WINDOW STYLE AND TURN OFF THE CLOSE BUTTON
       lStyle = GetWindowLong(hWnd, GWL_STYLE)
       SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)
    '------------------------------
    'OPEN THE FORM WITH THE TEXTBOX HIDDEN
       TextBox1.Visible = False
    End Sub
    
    
    Private Sub CommandButton1_Click()
    '------------------------------
    'CLOSE THE FORM AND EXIT THE APPLICATION
    Unload Me
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2016-03-17 at 22:22.

  7. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-03-18),RetiredGeek (2016-03-18)

  8. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    I'm studying both of these files. Thank you, thank you. This is just what I was looking for.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •