PDA

View Full Version : VBA to disable close



riteoh
08-18-2009, 06:34 PM
I have a sheet in a workbook that I need to disable the close x in the top right hand corner, for both the sheet and Excel.

I have googled and can see many codes on how to disable the close x for a form, but can't see a way that I can disable the code for a worksheet. Ideally when that worksheet is opened I would want something like (on worksheet open, blah blah, disable close button).

I have a button on the worksheet that when pressed will restore the close button (ideally).

Can anybody give some advice please?

Thanks in eager anticipation!

lucas
08-19-2009, 10:09 AM
This is what I use. Put this code in the thisworkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not bCloseOK Then
MsgBox "Please close the workbook by pressing" & _
vbNewLine & " the close button provided on " & _
vbNewLine & " the sheet named Close Workbook"
Cancel = True
' setting cancel to true cancels the close
End If
End Sub


You need to give them a button or something to be able to close the workbook though.

Private Sub CommandButton1_Click()
bCloseOK = True
Application.DisplayAlerts = False
Application.Quit
End Sub

riteoh
08-19-2009, 03:23 PM
Great suggestion - thanks. It is working as far as disabling the close button, however when I press my button to close the worksheet, the same window prompt is appearing to say close the worksheet via the button. What am I doing wrong?

Aussiebear
08-19-2009, 03:29 PM
Post your workbook so we can see what you currently have.

riteoh
08-19-2009, 03:32 PM
Here you go.... (hope I have attached correctly)

rbrhodes
08-19-2009, 04:33 PM
Hi riteoh,

new member! You need to delcare bCloseOK as public (or pass it to the WorkbookClose sub)

Public: In your module add this line at the top (before anything)

Public bCloseOK as boolean


It's that easy.

riteoh
08-19-2009, 04:53 PM
Thanks very much for the help.
I have 2 buttons on my spreadsheet.
The first that refers the user back to the previous screen (worksheet), the second that quits excel.
Obviously the code that you provided will quit the application, but given that the close button is disabled, how do I write the code to re-enable it when going back to a previous sheet.
I thought it might be as simple as adding the line bCloseOK=True but that doesn't seem to work.

Thanks :)

rbrhodes
08-19-2009, 05:05 PM
In the sheet module that you're returning to:

1) Open the VBE

2) Select the sheet you want the code to work on

3) The box at the top lrft of the code window will say 'General'. Click for the dropdown list and choos 'Worksheet' Then in the box that now says 'SelectionChange', click for the dropdown list and choose 'Activate'. Set bCloseOk in the resulting sub. Close the VBE and save the WB.

Here's an example:

Private Sub Worksheet_Activate()
bCloseOK = False
End Sub

Aussiebear
08-19-2009, 05:25 PM
Hi riteoh, In your code you have both sections of code starting as Public rather than Private. I would also remove the module 1 and place that code within the sheet that the button resides on.

Place this in the This workbook section


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not bCloseOK Then
MsgBox "Please close the workbook by pressing" & _
vbNewLine & " the close button provided on " & _
vbNewLine & " the sheet named Close Workbook" Cancel = True
' setting cancel to true cancels the close
End If
End Sub

and this in the Sheet 1 section

Public bClose As Boolean

Private Sub CommandButton1_Click()
bCloseOK = True
Application.DisplayAlerts = False
Application.Quit
End Sub

lucas
08-19-2009, 08:15 PM
riteoh, I apologize for not getting back to you sooner on this. I'm glad dr came along.

riteoh
08-19-2009, 10:07 PM
Thanks so much for your help.

I'm a novice at VBA - I watch what others do and learn from that.

My latest attempt is attached - it will give you an idea of what I am trying to establish, but can't see where I am going wrong still!

HELP !!!!!

Thanks :)

GTO
08-19-2009, 10:40 PM
Hi riteoh,

Can you describe what is not happening (or is happening that shouldn't be)?

Mark

riteoh
08-19-2009, 11:41 PM
On sheet 1 I have 2 buttons - one to exit excel, the other to go to the worksheet 'Othersheet'. When I press the button to go to othersheet, I want to be able to close excel the normal way - by pressing the x button - however at the moment when I have this button disabled, it is not being reactivated - so what code do I need on my othersheet button to re-enable the close worksheet function?

Aussiebear
08-20-2009, 12:16 AM
The main code has been placed in the "This workbook" module. Try moving it to the "Sheet 1" module.

riteoh
08-20-2009, 02:17 AM
Aussiebear,

Appreciate the help - thanks, but I must be missing something.
The spreadsheet you have attached still closes from Sheet1 if I press the close x in top right hand corner. Previous versions have stopped the workbook closing.

What are we doing wrong?

Thanks again.