Disable exit on selected tabs

I have a workbook with 3 tabs. The first tab is called Log-In, the 2nd tab is called "Unverified", and the third tab is called "Verified". I need to be able to force the user to use an "Exit" button I have created instead of being to close the worksheet with the Close "X" on the "Unverified" and "Verified" tabs. The Exit button runs a macro that formats and saves the worksheet.

However I need to allow the close function to occur on the tab named Log-In. I have created the following macro and placed the code in This Workbook. Unfortunately I am a novice at VBA and despite my efforts it does not work.

Can anyone help?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveSheet.Name = "UNVERIFIED" Then
MsgBox "YOU MUST USE EXIT BUTTON"
Cancel = True
End
End If
If ActiveSheet.Name = "VERIFIED" Then
MsgBox "YOU MUST USE EXIT BUTTON"
Cancel = True
End
Else
ActiveWorkbook.Close
End If
   
End Sub

Hello Randy,

The following code placed in the "Workbook" module should do the job for you:-

Public myNotice As Boolean

 Private Sub Workbook_BeforeClose(Cancel As Boolean)

 If myNotice = True Then Exit Sub
 If ActiveSheet.Name = "Log-In" Then Exit Sub
 Application.DisplayAlerts = False
 Cancel = True
 Beep
 MsgBox "YOU MUST USE EXIT BUTTON!", vbExclamation, "WARNING!"
 
 End Sub

It should leave your "Log-In" sheet to close as per normal. The other two sheets will be blocked from the normal "close" procedure with your Exit button taking over from there.

I hope that this helps.

Regards,
vcoolio.

This worked perfectly. Thanks much!

vcoolio,

Is there also a way of preventing the user from using the "Save" or "Save-As" Excel functions for all tabs but the "Log-In" page?

Thanks,

Randy

Hello Randy,

I think that the above code pretty well covers it. Save, Save As... is part of the Close(X) function so the above code covers the entire work book except the exempt sheets (such as "Log-In").

The following code (and there are a number of variations for such a function) would work also:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    MsgBox "Your message here."
    Cancel = True

End Sub

However, if you have a look at the following thread:-

http://www.excelkey.com/forum/viewtopic.php?f=3&t=1620

you may begin to understand the complexities of such a request and the "round-a-bout" issues that can arise.

Keep it simple, Randy.

Cheerio,
vcoolio.