hiding the "X" or close button on user form

That close button cannot be hidden as far as I know, but it can be neutralized with code akin to this example (modify for the reference to my habit of placing colorful "can't miss them" command buttons on my forms):

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "To exit this form, please click either" & vbCrLf & _
"the green ''Proceed with report'' button," & vbCrLf & _
"or the red ''Cancel'' button.", 16, "The ''X'' close button is disabled."
Cancel = True
End If
End Sub

Tom, please use the code tags.

Re: hiding the "X" or close button on user form

Hi all,

I need to "grey" out the exit button on a workbook that is shared with others. Using the "X" button at the top right hand corner results in errors. Exiting via a command button that I have added to the toolbars lets the Excel environment be reinstated and exits this workbook gracefully.

I have added the Sub Userform_QueryClose and it does not seem to be doing the job. In testing, I was still able to close the workbook via the "X" at the top. What might I be overlooking?

Thanks much!

Re: hiding the "X" or close button on user form

Have a look here:

http://www.xcelfiles.com/SplashScrn.html

This was given me by Andy Pope in answer to my similar question.

Re: hiding the "X" or close button on user form

How about

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
    MsgBox "Sorry, you cannot close the Workbook in this way"
End Sub

Or, protect the Workbook for Windows.

Having said all this though, you should really address the problem caused by a user closing a Workbook in a normal way.

Re: hiding the "X" or close button on user form

Thanks so much for your response. Yes, I agree that the code needs to be modified so that there is no error with a normal close using the "X" however this is a huge workbook with a lot of code. I have no idea how to start looking for the problem. I know just enough to make me truly dangerous! LOL

Thanks much!

Re: hiding the "X" or close button on user form

Thanks Dave,

This does exactly what I want it to do! I need to keep the users from closing excel with the Excek "X" or the File "X". The problem is, if I have this in the workbook now my macros can't close the file either. How do I temporarily disable it while My macro executes?

Thanks, in advance

Re: hiding the "X" or close button on user form

The Macro resides in a separate Excel Worksheet. The macro is being called by another application operating on the same computer.

Re: hiding the "X" or close button on user form

Record a Macro turning off the Workbook Protection.

Re: hiding the "X" or close button on user form

Quote from Dave Hawley

How about

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
    MsgBox "Sorry, you cannot close the Workbook in this way"
End Sub

Or, protect the Workbook for Windows.

Having said all this though, you should really address the problem caused by a user closing a Workbook in a normal way.

This is the solution I would prefer to use. Protecting the workbook does eliminate the File Close "X", but the user can still close Excel. I wish I had some way of addressing the problem, but it's not an in house project & I have no control over the users. Most of them are not very "computer friendly" anyway.

Thanks Again,

Re: hiding the "X" or close button on user form

I can't think of anyway to avoid this because you are cancelling the code. What is actually needed before closing?

Re: hiding the "X" or close button on user form

OK, I'll give you the whole scenario, pardon me for being long winded!

In this application I am using Excel to display live report data from a PLC. The user actually monitor's the process through another software package (RSView32). Certain events throughout the day cause RSView to trigger a macro that launches into the open XL file, saves the file with a sequential filename, closes the file, & starts a new file from an .xlt template. The problem I'm experiencing is that if the user view the data, then closes the XL file; when the macro triggers, if the file isn't open it hangs up. There is really no reason for the user to ever close the file, I think they just do it out of habit, or by accident.

Re: hiding the "X" or close button on user form

Can't you just call the macro in the Before_Close

Re: hiding the "X" or close button on user form

Thanks for the help Roy, but I'm not sure I follow your last post. Can you elaborate?

Re: hiding the "X" or close button on user form

After re-reading your post, maybe the only way is to remind the user not to close the Workbook,

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)


    Select Case MsgBox("It is not advisable to close this workbook! Are you sure?", vbYesNo Or vbCritical Or vbDefaultButton1, "Do not close")


        Case vbYes
            GoTo UserCloses
        Case vbNo
            Cancel = True
    End Select
UserCloses:
End Sub

Re: hiding the "X" or close button on user form

OK That works for me,
Now the question is how can I set the macro to "Auto-Respond" to the message box we just created. If the computer is unattended during the macro execution, nothing happens because the msgbox is waiting for an "Are You Sure?" response from the operator.

Re: hiding the "X" or close button on user form

Wouldn't that defeat the purpose of asking? They might want to click "No" but "Yes" is clicked for them.

Re: hiding the "X" or close button on user form

Thanks for chiming in Dave,
What I'm actually looking for is to stop the users from closing the workbook, or XL as a whole, but to allow the macro's to still close the file if need be.

The macro knows what it's doing & what needs to be re-opened after a close, unfortunately I can't necessarily say the same for the users.;)

Re: hiding the "X" or close button on user form

maybe you can set a flag as Andy Pope suggested in an earlier post. I'll see if i can find what he wrote