Disabling X to prevent closing of Spreadsheet

[alexdurc09]

Hello,
Can someone help me please – I would like to stop peoplefrom closing my spreadsheet with the X buttons in the top corner of the screen.I want them to use a button which when clicked saves and also closes thespreadsheet.
I have tried looking on Google but all I can find is code todisable to X buttons and nothing which allows my button to work.
Thanks,
Alex

 

[gallen]

You need some simple code in the 'ThisWorkbook' object of the VBA environment.

This is very basic but works. Before the workbook closes, the variable 'allowed' must be set to true. So only set it to true via the button. If anyone clicks the close button they will get the message and close will be cancelled

Public allowed As Boolean



Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If allowed = False Then
        MsgBox "Please use button", vbInformation, "Closing not allowed"
        Cancel = True
    End If
End Sub

Edit: To clarify, 'allowed' will not be directly visible unless you place it in a standard module. If it is in Thisworkbook you will need

ThisWorkbook.allowed = True

in your button code

 

[alexdurc09]

That's great, it has worked a treat! Thank you again

 

[alexdurc09]

Don't suppose you know how to get auto filters to work on a spreadsheet which is protected with vba? Code is below -

Private Sub UpdateRecord_Click()
Sheet1.Unprotect Password:="manlog"
Sheet7.Unprotect Password:="manlog"
If Me.Reg1.Value = "" Then
MsgBox "Container Number Can Not be Blank!",vbExclamation, "Container Number"
Exit Sub
End If
CONTAINERNUMBER = Me.Reg1.Value
Sheets("Containers").Select
Dim rowselect As Double
Dim findrow As Range
Dim lastRowHistory As Long

Set findrow =Worksheets("Containers").Range("A:A").Find(what:=Me.Reg1.Value,LookIn:=xlValues)

rowselect = findrow.Row

'move current record to history

lastRowHistory = Worksheets("HistoricalRecords").Cells(Rows.Count, "A").End(xlUp).Row
lastRowHistory = lastRowHistory + 1

Rows(rowselect).Select
Selection.Copy
Sheets("Historical Records").Select
Rows(lastRowHistory).Select
ActiveSheet.Paste
Sheets("Containers").Select

'rowselect = Me.Reg1.Text
'rowselect = rowselect + 1
Rows(rowselect).Select
Cells(rowselect, 2) = Me.Reg2.Text
Cells(rowselect, 3) = Me.Reg3.Text
Cells(rowselect, 4) = Me.Reg4.Text
Cells(rowselect, 5) = Me.Reg5.Text
Cells(rowselect, 6) = Me.Reg6.Text
Cells(rowselect, 7) = Me.Reg7.Text
Cells(rowselect, 8) = Me.Reg8.Text
Cells(rowselect, 9) = Me.Reg9.Text
Cells(rowselect, 10) = Me.Reg10.Text
Cells(rowselect, 11) = Me.Reg11.Text
Cells(rowselect, 12) = Me.Reg12.Text
Cells(rowselect, 13) = Me.Reg13.Text
Cells(rowselect, 14) = Me.Reg14.Text

MsgBox "Container updated!"
Sheets("Main Screen").Select
Unload Me
Sheet1.Protect Password:="manlog"
Sheet7.Protect Password:="manlog"

End Sub

 

[gallen]

I'm a little confused, may be better to post another thread for this issue. Please wrap your code in the code tags. It makes it much easier to read and increases the chances of someone offering their help.

Thanks

 

[alexdurc09]

Sorry - I was asking on the off chance, I have already put a post on which can be found here: -

https://www.mrexcel.com/forum/excel-questions/1076543-using-auto-filter-when-protected-vba.html

Thanks
Alex