Disable Close Button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wanted to disable the close button in the upper right hand corner of Excel
so users would have to use a button on the spreadsheet (assigned to a macro)
to close the worksheet. I used the following code:

Private Sub WorkBook_BeforeClose (Cancel As Boolean)
Cancel = True
End Sub

The code works great but now my macro button will not work and I can't close
the worksheet at all. Can you help me out? I'd appreciate it!
 
In your code that closes the workbook, you'll want to make sure that you don't
allow the workbook_beforeclose event to run normally (cancel = true).

I'd put this in at the top of a General Module:

Public BlkProc as Boolean

By declaring this variable outside a procedure and making it public, each
routine can read the value in that variable.

Then in your code that closes the workbook:

BlkProc = true
thisworkbook.close savechanges:=true 'or false???

And then change the workbook_beforeclose event to look at that variable:

Private Sub WorkBook_BeforeClose (Cancel As Boolean)
if blkproc = true then
'do nothing, let the workbook close
else
Cancel = True
end if
End Sub
 
Dave

Thank you for your response. I follow everything you said except the General
Module part. I'm not sure what a general module is and exactly where to put
the code
Public BlkProc as Boolean. I put it in the ThisWorkbook module but it is
still not responding.
 
Select your project in the VBE.
Insert|module
and put the declaration there.
Dave

Thank you for your response. I follow everything you said except the General
Module part. I'm not sure what a general module is and exactly where to put
the code
Public BlkProc as Boolean. I put it in the ThisWorkbook module but it is
still not responding.
Click to expand...
 
Dave,

It is working perfectly! Thank you so much. I really appreciate your help
and quick response.
 
Back
Top