Microsoft Excel Webinar

Results 1 to 2 of 2

Thread: Exit Userform and close excel

  1. #1

    Exit Userform and close excel

    Hello, I want to prevent the user from accessing the excel spreadsheets.
    therefore I disable the close command (the cross in the upper right corner to close a window) and ask him to use a cancel button.

    What should happen when user exits the userform:
    The user will then be asked if he really wants to close or not. There he can choose between OKAY and Cancel. If he presses okay he will exit the Userform and Excel will close automatically. However, If he presses Cancel he should stay on the userform.

    My problem:
    When the user is asked if he "really wants to close" and presses the cancel button, excel stays open and the userform closes. BUT the userform should stay open and excel should be hidden....

    Here is my code

    In this workbook:

    VB:
    Private Sub Workbook_open() 
        Application.WindowState = xlMinimized 'user cannot see what is entered in Excel
        AppActivate ("Microsoft Excel") 
        UserForm1.Show 
         
    End Sub 
     
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean) 
        If (MsgBox(prompt:=" Do you really want to close?", Buttons:=vbOKCancel) = vbCancel) Then 
            Cancel = True 
        Else 
            Cancel = False 
        End If 
         
        If Cancel = False Then 
            Application.Quit 
        End If 
         
    End Sub 
    
    
    Formatting tags added by mark007
    and in the UserForm I used this code for the cancel Button:
    VB:
     
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
        If CloseMode <> 1 Then Cancel = 1 
        MsgBox ("Please click the Cancel button to exit the program") 
    End Sub 
     
    Private Sub CMD_Cancel_Click() 
        ActiveWorkbook.Close savechanges = True 
        Unload Me 
    End Sub 
    
    
    Formatting tags added by mark007

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,948
    Location
    How about doing it all in the form

    VB:
     
    Private Sub CMD_Cancel_Click() 
        If MsgBox(prompt:=" Do you really want to close?", Buttons:=vbOKCancel) = vbOK Then 
             
            ActiveWorkbook.Save 
            Application.Quit 
        End If 
    End Sub 
     
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
        If CloseMode = vbFormControlMenu Then 
             
            Cancel = True 
            Call CMD_Cancel_Click 
        End If 
    End Sub 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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