Hey guys, i have seen this somewhere before but i couldn't find it. I need to hide the "X" or close button on a user form so that users have to use the next or ok buttons that i have added, can anyone help me on this or give me a link.
Thanks
Hey guys, i have seen this somewhere before but i couldn't find it. I need to hide the "X" or close button on a user form so that users have to use the next or ok buttons that i have added, can anyone help me on this or give me a link.
Thanks
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.
Tom,
thanks, that will do the trick for me just fine
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 HawleyHow about
CodePrivate 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
Display More
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
Don’t have an account yet? Register yourself now and be a part of our community!