Excel Hints

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:

    [VBA]
    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
    [/VBA]

    and in the UserForm I used this code for the cancel Button:
    [VBA]

    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

    [/VBA]

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

    [vba]

    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
    [/vba]
    ____________________________________________
    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
  •