VBA - CloseMode to not allow use of "X" close button

[AAF4348]

Hi all. I am trying to force users of a spreadsheet to use a CLOSE button I have added to the workbook. When the button is clicked, the following code is used:

Sub Clse_Sve()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CutCopyMode = False

Sheets("UserTRX").Visible = True
Sheets("UserTRX").Select
Set mycell = Cells(65000, 1).End(xlUp).Offset(1, 0)
mycell.Select
mycell.Formula = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set mycell2 = ActiveCell.Offset(0, 1)
Set mycell3 = ActiveCell.Offset(0, 2)
Set mycell4 = ActiveCell.Offset(0, 3)
mycell2.Select
ActiveCell.Value = UserNameWindows_c
mycell3.Select
ActiveCell.Value = ComputerNameWindows_c
mycell4.Select
ActiveCell.Value = "File Closed"
Range("A1").Select
Sheets("Costing").Select
Sheets("UserTRX").Visible = False

Range("A11").Select

ActiveWorkbook.Save
ThisWorkbook.Close
End Sub
--------------------------------------------------------
Function UserNameWindows_c() As String
UserNameWindows_c = Environ("username")
End Function
--------------------------------------------------------
Function ComputerNameWindows_c() As String
ComputerNameWindows_c = Environ("computername")
End Function

I have the following Private sub loaded as well to keep the "X" close button from being used:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CloseMode As Integer
If CloseMode = 0 Then
MsgBox "Please use the CLOSE button."
Cancel = True
Else: End If
End Sub

When I am testing the code using F8 to run through each line, the Cls_Sve sub works fine and takes me to the private sub. This will then give me the MsgBox and cancel the closing of the workbook.

Any ideas? Thanks in advance!!!!

 

[AAF4348]

 

[AAF4348]

I figured it out!! I simply put the code I wanted to have done in the Private Sub BeforeClose procedure:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CutCopyMode = False

Sheets("UserTRX").Visible = True
Sheets("UserTRX").Select
Set mycell = Cells(65000, 1).End(xlUp).Offset(1, 0)
mycell.Select
mycell.Formula = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set mycell2 = ActiveCell.Offset(0, 1)
Set mycell3 = ActiveCell.Offset(0, 2)
Set mycell4 = ActiveCell.Offset(0, 3)
mycell2.Select
ActiveCell.Value = UserNameWindows_c
mycell3.Select
ActiveCell.Value = ComputerNameWindows_c
mycell4.Select
ActiveCell.Value = "File Closed"
Range("A1").Select
Sheets("Costing").Select
Sheets("UserTRX").Visible = False

Range("A11").Select

ActiveWorkbook.Save

End Sub
--------------------------------------------------
Function UserNameWindows_c() As String
UserNameWindows_c = Environ("username")
End Function
-----------------------------------------------------
Function ComputerNameWindows_c() As String
ComputerNameWindows_c = Environ("computername")
End Function

Works like a charm!