Help with the Close Button on a UserForm

[Anne P.]

I have a template named "SK Interoffice Memorandum.dot." The userform has a
command button named Cancel which contains the following code:

If MsgBox("Are you sure?", vbYesNo + vbInformation, _

"Seward & Kissel Interoffice Memo") = vbYes Then

Unload Me

ActiveDocument.Close (wdDoNotSaveChanges)

End If



After clicking the Cancel button, if No is clicked on the message box, the
focus is returned to the userform, but if Yes is clicked, the form is
unloaded and the document is closed without saving changes.



The client now wants the same actions to occur when the user clicks the X
button on the userform. I added the following code to the form to effect
this:



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then Cancel = False

If MsgBox("Are you sure?", vbYesNo + vbInformation, _

"Seward & Kissel Interoffice Memo") = vbYes Then

Unload Me

ActiveDocument.Close (wdDoNotSaveChanges)

End If

End Sub



After adding the UserForm_QueryClose event as above, I get the following
results: After clicking the X button, if No is clicked on the message box,
it unloads the userform and leaves the document open in Word, when it should
return focus to the userform. If the user clicks Yes, it unloads the
userform and closes the document; however, it leaves a temporary file in the
same directory where the original template is stored (named ~$ Interoffice
Memo.dot). Also, the behavior of the Cancel button has now changed to the
following: If No is clicked, the focus is returned to the userform as
expected. If Yes is clicked, nothing happens, then if Yes is clicked a
second time, the userform is unloaded and the document is removed from the
screen. However, it appears that Word is trying to remove the document a
second time because I get an error message that says: "Command is not
available because no document is open".



I then changed the QueryClose event as follows:



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then

Cancel = False

If MsgBox("Are you sure?", vbYesNo + vbInformation, _

"Seward & Kissel Interoffice Memo") = vbYes Then

Unload Me

ActiveDocument.Close (wdDoNotSaveChanges)

End If

Else

Cancel = True

End If

End Sub



I now get the following results: Clicking the X button works the same as
before when clicking either No or Yes. The behavior of the Cancel button
has now changed to the following: If No is clicked, the focus is returned
to the userform as expected. If Yes is clicked, the document is removed
from the screen but the userform is still there. Then the only way to close
the userform is to click the X button and choose Yes, at which point I get a
debug error 4148 stating "Command is not available because no document is
open".



Any insights on how I can make this work whether the X button or Cancel
button is clicked? Thanks,



Anne P.

 

[Jean-Guy Marcil]

Anne P. was telling us:
Anne P. nous racontait que :

I have a template named "SK Interoffice Memorandum.dot." The
userform has a command button named Cancel which contains the
following code:
If MsgBox("Are you sure?", vbYesNo + vbInformation, _

"Seward & Kissel Interoffice Memo") = vbYes Then

Unload Me

ActiveDocument.Close (wdDoNotSaveChanges)

End If



After clicking the Cancel button, if No is clicked on the message
box, the focus is returned to the userform, but if Yes is clicked,
the form is unloaded and the document is closed without saving
changes.

Here is a simple way of doing this:
'Declare a module variable at the top of the code:

Dim Leave_OK As Boolean
'_______________________________________
Private Sub CommandButton1_Click()

'Set that variable to false opnly if the Command button is clicked:
Leave_OK = True
Unload Me

End Sub
'_______________________________________

'_______________________________________
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'If the variable has not been set to true by the command button, Cancel
If Not Leave_OK Then Cancel = True

End Sub
'_______________________________________


Alternatively, disable the "X" altogether.

Put this at the top of the userform code:

'_______________________________________
Private Declare Function _
GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, _
ByVal bRevert As Long) As Long

Private Declare Function _
GetMenuItemCount Lib "user32" _
(ByVal hMenu As Long) As Long

Private Declare Function _
DrawMenuBar Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function _
RemoveMenu Lib "user32" _
(ByVal hMenu As Long, _
ByVal nPosition As Long, _
ByVal wFlags As Long) As Long

Private Declare Function _
GetActiveWindow Lib _
"user32" () As Long

Const MF_BYPOSITION = &H400&
Const MF_REMOVE = &H1000&
'_______________________________________

And then use this Activate code:
'_______________________________________
Private Sub UserForm_Activate()
Dim hSysMenu As Long
Dim nCnt As Long
Dim lWin

lWin = GetActiveWindow()
hSysMenu = GetSystemMenu(lWin, False)
Stop
If hSysMenu Then
nCnt = GetMenuItemCount(hSysMenu)
If nCnt Then
RemoveMenu hSysMenu, nCnt - 1, _
MF_BYPOSITION Or MF_REMOVE
RemoveMenu hSysMenu, nCnt - 2, _
MF_BYPOSITION Or MF_REMOVE
DrawMenuBar lWin
End If
End If
End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Anne P.]

Jean-Guy,

I just found a post through Google on another VBA forum where Jay Freedman
suggested the following:

An alternative would be to assume that the X button should be the equivalent
of the Cancel button, and just do it:

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
btnCancel_Click
End If
End Sub

I did this in my template (renaming btnCancel_Click to cmdCancel_Click to
match my button).
Now if I click the Cancel button, choosing either Yes or No works as
expected. However, clicking the X button shows the message box and if I
click No it closes the userform and leaves the newly created document on the
screen. It should not be closing the userform. If I click Yes it unloads
the userform, closes the newly created document, but leaves the temporary
file mentioned previously in the template location.

Disabling the X button is not an option. The client does not want to fully
disable it.

Thanks,

Anne P.

 

[Jean-Guy Marcil]

Anne P. was telling us:
Anne P. nous racontait que :

Jean-Guy,

I just found a post through Google on another VBA forum where Jay
Freedman suggested the following:

An alternative would be to assume that the X button should be the
equivalent of the Cancel button, and just do it:

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
btnCancel_Click
End If
End Sub

I did this in my template (renaming btnCancel_Click to
cmdCancel_Click to match my button).
Now if I click the Cancel button, choosing either Yes or No works as
expected. However, clicking the X button shows the message box and
if I click No it closes the userform and leaves the newly created
document on the screen. It should not be closing the userform. If I
click Yes it unloads the userform, closes the newly created document,
but leaves the temporary file mentioned previously in the template
location.

Try my alternative.

Disabling the X button is not an option. The client does not want to
fully disable it.

Strange, because you are in fact disabling it...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Anne P.]

Jean-Guy,

I tried your first method. The cancel button now works as I want it to.
What is happening with the X button, however, is that when you click it,
nothing happens. This is going to result in a lot of calls to the Help Desk
reporting that their Close button is "broken". The firm that I am writing
these templates for have over 500 users, most of whom will not realize it is
meant to work this way, but will think that the button is broken. That is
why I wanted to display the same message box they get when they click the
Cancel button.

I am assuming (possibly, incorrrectly) that the second method you mentioned
(using the API calls) will act in the same manner.

Thanks,
Anne P.

 

[Jean-Guy Marcil]

Anne P. was telling us:
Anne P. nous racontait que :

Jean-Guy,

I tried your first method. The cancel button now works as I want it
to. What is happening with the X button, however, is that when you
click it, nothing happens. This is going to result in a lot of calls

You could add a MsgBox informing the user to use the Cancel button instead:

If Not Leave_OK Then
MsgBox "Please, use the ""Cancel"" button instead.", _
vbInformation, "Unavailable"
Cancel = True
End If

to the Help Desk reporting that their Close button is "broken". The
firm that I am writing these templates for have over 500 users, most
of whom will not realize it is meant to work this way, but will think
that the button is broken. That is why I wanted to display the same
message box they get when they click the Cancel button.

I am assuming (possibly, incorrrectly) that the second method you
mentioned (using the API calls) will act in the same manner.

What it does is to make the "X" clearly unavailable. So users should
understand that they cannot use it.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jonathan West]

Anne P. was telling us:
Anne P. nous racontait que :


You could add a MsgBox informing the user to use the Cancel button
instead:

If Not Leave_OK Then
MsgBox "Please, use the ""Cancel"" button instead.", _
vbInformation, "Unavailable"
Cancel = True
End If



What it does is to make the "X" clearly unavailable. So users should
understand that they cannot use it.

Alternatively you can even set it so that it is greyed out and disabled.
Take a look here for how to do it.

How to disable the X close button on a UserForm
http://word.mvps.org/FAQs/Userforms/DisableClose.htm


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Anne P.]

Hi Jonathan,

I have a question about the following article:

How to disable the X close button on a UserForm
http://word.mvps.org/FAQs/Userforms/DisableClose.htm

All of the functions are marked as private. Can they be made public and
placed in a global template so that I can call them from different
templates, or does the code have to go into each template where I want the
button disabled?

Thanks,
Anne P.

 

[Jonathan West]

Hi Jonathan,

I have a question about the following article:

How to disable the X close button on a UserForm
http://word.mvps.org/FAQs/Userforms/DisableClose.htm

All of the functions are marked as private. Can they be made public and
placed in a global template so that I can call them from different
templates, or does the code have to go into each template where I want the
button disabled?

If you want the code written once and called from every userform in a
template that needs the button disabled, you could proceed as follows

1. Copy all the code into a separate module

2. Rename the UserForm_Initialize routine (e.g. to DisableCloseButton) and
make it public. Do not make any of the other items in the module public.

3. Include a call to DisableCloseButton in the UserForm_Initialize event of
each form whose X button you want disabled.


I haven't tested this, but I suspect that trying to call the code from
another template using Application.Run will not work, because I suspect that
the Me object variable will not keep its value across templates. This is
something you could test though.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Anne P.]

Jonathan,

I only have one userform per template (for the most part). What I want is
this: I have a global template named SKGlobal.dot. I want to store the
info to disable the close button in that template and be able to call it
from each tempalte that needs to have the close button disabled.

My global templates are stored in C:\Program Files\Microsoft
Office\Office11\Startup. I know this is normally not trusted, that the
Startup directory should be: C:\Documents and Settings\username\Application
Data\Microsoft\... However, I have purchased a Digital ID from Verisign to
sign my code, so using the Program Files choice will work. This way I can
set a reference to the Global template and easily call macros and functions
from that template.

So my question is this: I have several templates (SK Memo.dot, SK Fax.dot,
etc.). In each of these templates, I want to disable the close button on
the userform. In the code from this article, can I place the code in
SKGlobal, change Private to Public, and call the functions from the various
tempaltes that need to use it?

Thanks,
Anne P.

 

[Jonathan West]

I did the testing I suggested that you do, and I can confirm that Word
complains about the use of the Me keyword when called from another template.

You can get round this by passing the Userform caption as a string parameter
to the DisableClose routine, and using that parameter in the FindWindow
command in place of Me.Caption.

the first few lines of the routine are as follows

Public Sub DisableClose(Caption As String)

Dim hMenu As Long
Dim menuItemCount As Long


'Obtain the window handle to the userform
hWnd = FindWindow(vbNullString, Caption)



From there on, it is the same as on the web page. You then include the
Initialize event for each userform, as follows

Private Sub UserForm_Initialize()
DisableClose Me.Caption
End Sub


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org