Disable Close Button

[Dan]

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!

 

[Dave Peterson]

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

 

[Dan]

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.

 

[Dave Peterson]

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.

 

[Dan]

Dave,

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

 

[Dave Peterson]

Glad you got it working.

Dave,

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