Member Avatar for shabadoo

I have a project that when opened automatically shows a userform for LOGIN/PASSWORD. Everything works great except that if the user clicks the Close(x) button included in the title bar of the userform, the userform is exited but the workbook is still open to its first page.

Is there any code snippet that will cause the project to close completely if the Close(X) Button is clicked to prevent entry into my program other than with proper LOGIN & USERNAME.

  • 5 Contributors
  • 7 Replies
  • 120 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by abelingaw

Dani AI

Generated

This thread has useful options already — pointed out unloading, showed an API route, suggested form properties, and offered a confirmation prompt — but none of those comments addressed a simple, robust pattern you can add that cleanly closes the workbook when the user clicks the title-bar X on the login form.

Use a modal login form plus a module-level flag that records successful authentication. Show the form in the Workbook_Open event, and catch the form Close action in the UserForm_QueryClose event. If the form closes without authentication, close the workbook (or quit Excel) immediately. Example (names are illustrative — replace frmLogin with your form name):

' In a standard module
Public gIsAuthenticated As Boolean
' In ThisWorkbook
Private Sub Workbook_Open()
    gIsAuthenticated = False
    frmLogin.Show vbModal
    If Not gIsAuthenticated Then
        ThisWorkbook.Close SaveChanges:=False
    End If
End Sub
' In the login form (catch the title-bar X)
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        gIsAuthenticated = False
        Unload Me
    End If
End Sub

On a successful login set gIsAuthenticated = True then Unload Me (or hide the form) so Workbook_Open continues. Additional notes: avoid using End because it halts VBA without cleanup; test behavior with macros disabled (Workbook_Open won't run); VBA login is convenient but not cryptographically secure — use workbook encryption or server-side auth for real security; protect the VBA project to reduce casual tampering but recognize it can be bypassed.

Recommended Answers

All 7 Replies

Member Avatar for WaltP

Unload the workbook form from the unload method in the password form.

Member Avatar for AndreRet

As WaltP pointed out, unload ALL forms in the application OR you can disable the window "x" button as in -

'In a module, add the following code...
Option Explicit

Private Const MF_BYPOSITION = &H400
  Private Const MF_REMOVE = &H1000

  Private Declare Function DrawMenuBar Lib "User32" (ByVal hWnd As Long) As Long

  Private Declare Function GetMenuItemCount Lib "User32" (ByVal hMenu As Long) As Long
  Private Declare Function GetSystemMenu Lib "User32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
  Private Declare Function RemoveMenu Lib "User32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Public Sub RemoveX(frm As Form)

  Dim hMenu As Long
  Dim menuItemCount As Long

  hMenu = GetSystemMenu(frm.hWnd, 0)
  
  If hMenu Then
      
      menuItemCount = GetMenuItemCount(hMenu)
    
      Call RemoveMenu(hMenu, menuItemCount - 1, MF_REMOVE Or MF_BYPOSITION)
   
      Call RemoveMenu(hMenu, menuItemCount - 2, MF_REMOVE Or MF_BYPOSITION)
    
      Call DrawMenuBar(frm.hWnd)
   End If
End Sub

Now just call the code on your form as in -

Call RemoveX (Me)

If you want to unload all forms, the following will apply -

'In a module the following...
Public Sub UnloadAllForms()

Dim Form As Form
   For Each Form In Forms
      Unload Form
      Set Form = Nothing
   Next Form
End Sub

'Call in your unload event of your form...

Call UnloadAllForms
Member Avatar for shabadoo

Solved, Thank you

Member Avatar for AndreRet

Pleasure. Please mark this thread as solved as well, thanks:)

Member Avatar for AndreRet

Please mark this as solved, found at the bottom of this page, thanks.:)

Member Avatar for exception

actually this code is more complicated. You are manipulating API for a simple PROBLEM,
if you have just observed, there are no programs that uses this function. i.e it's very unprofessional.

set the Form to This:

Form1.ControlBox = False
Form1.BorderStyle = 1

if you don't put a caption in the form, the title bar wont appear
let's complicate this.

Member Avatar for abelingaw

You can also add this code to alert a user before closing a (any) form.

Private Sub Form_Unload(Cancel As Integer)

Dim strMess
If LogOff = False Then

    strMess = "You are about to close the Automated Payroll System." & vbCrLf & vbCrLf & "Are you sure?"
    
    If MsgBox(strMess, vbQuestion + vbYesNo, "Exit Confirmation") = vbYes Then
        End
        
    Else
        
        Cancel = 1
        
    End If
    
ElseIf LogOff = True Then

    Unload Me
    Form1.Show
    
End If

End Sub

Please mark thread as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.