Disabling X to prevent closing of Spreadsheet

alexdurc09

New Member
Joined
Sep 21, 2018
Messages
17
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
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

Code:
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
Code:
ThisWorkbook.allowed = True
in your button code
 
Last edited:

alexdurc09

New Member
Joined
Sep 21, 2018
Messages
17
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

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,013
Messages
5,526,268
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top