Warning Message on Excel Exit

[stevembe]

With help through these forums I have managed to set up the following code that hides/unhides a single work sheet in a workbook and is password protected (password deleted):

Sub ToggleCompSheets()
Dim ConfirmHide As String
Application.ScreenUpdating = False
If Sheets("HR Data").Visible = xlSheetVeryHidden Then
GetCompApproval
Sheets("HR Data").Visible = xlSheetVisible
Sheets("HR Data").Activate
Else
ConfirmHide = MsgBox(prompt:="This will hide the HR Data tab." & vbCr & vbCr & _
"You will need a password to access " & vbCr & _
"the tab if you choose to continue." & vbCr & vbCr & _
"Do you wish to continue?", Buttons:=vbYesNo, _
Title:="Hide HR Data Tab")
If ConfirmHide = vbNo Then
End
Else
Sheets("HR Data").Visible = xlSheetVeryHidden
Sheets("Index").Activate
'Range("c8").Select
End If
End If
End Sub

Sub GetCompApproval()
Dim Password As String
Dim RetryPass As String
Password = InputBox(prompt:="Enter password to display HR Data tab", Title:="Password")
'MsgBox prompt:=Password
If Password = "" Then
End
ElseIf Password <> "" Then
RetryPass = MsgBox(prompt:="Sorry, password is incorrect" & vbCr & _
"contact Steve on Ext ****" & vbCr & _
"if you need a password", _
Title:="Access Denied", _
Buttons:=vbRetryCancel + vbExclamation)
If RetryPass = vbRetry Then
GetCompApproval
Else
End
End If
Else
Sheets("HR Data").Visible = xlSheetVisible
Sheets("HR Data").Activate
End If
End Sub

My problem is that if users who have access to the worksheet forget to hide it before closing or exit the sheet will be visible to anybody when opened again. I am after a reminder pop up on exit or closure that states "Please ensure the hidden tab has been hidden" and with three button options as follows:

Worksheet Hidden Continue with Exit (Action closes excel)

Return to Workbook (Returns the user to workbook)

Non Applicable (closes excel, this is for users who do not have access to the spreadsheet)

Can anybody advise, thanks in advance.

Steve

 

[stevembe]

Or hide sheet on exit!