I have a userform for which I disable the close button on the caption
to force people to use my close button. When I include a line to run
my close button in the following code I get an error when I close
Excel. The error does not pop up when the form and file close, only
afterward when I close Excel. Then I get Run Time Error 440,
Automation Error. I only get the options to End or Help. End clears
things up, Help is pretty useless.

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

If CloseMode = 0 Then
Call CommandButton1_Click
Cancel = 1
End If

End Sub

Originally the functionality in the CommandButton1_Click code was in
the UserForm_QueryClose() code. I get the same result including it or
running it indirectly. I do not get the Automation error when exiting
Excel when the code is run from the close command button. This is all
the command button code is:

Private Sub CommandButton1_Click()

Dim x As Integer

'exit button

Unload Me

100

On Error GoTo 200

If Range("AutoClose").Value = "On" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

Exit Sub

200

x = MsgBox("can't save file right now. Close without saving?",
vbYesNo)

If x = 6 Then
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Else
GoTo 100
End If

End Sub

This just closes the file if a flag is set to do so. Sometimes the
network is slow and we get an error about the file being in use, so
the last part of the code deals with that.

I only get the automation error when closing Excel. It only happens
when I try to use my command button 1 code from the
UserForm_QueryClose code. Any ideas?

Thanks

Ken

Some things to try...
1. Move Cancel =1 before Call CommandButton1_Click
2. If you have an RefEdit control on the form in a frame - remove the frame.
3. If you have a RefEdit control on the form (without a frame) remove the control (use an input
box).
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)



"Ken"
wrote in message
...
I have a userform for which I disable the close button on the caption
to force people to use my close button. When I include a line to run
my close button in the following code I get an error when I close
Excel. The error does not pop up when the form and file close, only
afterward when I close Excel. Then I get Run Time Error 440,
Automation Error. I only get the options to End or Help. End clears
things up, Help is pretty useless.

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

If CloseMode = 0 Then
Call CommandButton1_Click
Cancel = 1
End If

End Sub

Originally the functionality in the CommandButton1_Click code was in
the UserForm_QueryClose() code. I get the same result including it or
running it indirectly. I do not get the Automation error when exiting
Excel when the code is run from the close command button. This is all
the command button code is:

Private Sub CommandButton1_Click()

Dim x As Integer

'exit button

Unload Me

100

On Error GoTo 200

If Range("AutoClose").Value = "On" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

Exit Sub

200

x = MsgBox("can't save file right now. Close without saving?",
vbYesNo)

If x = 6 Then
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Else
GoTo 100
End If

End Sub

This just closes the file if a flag is set to do so. Sometimes the
network is slow and we get an error about the file being in use, so
the last part of the code deals with that.

I only get the automation error when closing Excel. It only happens
when I try to use my command button 1 code from the
UserForm_QueryClose code. Any ideas?

Thanks

Ken

Jim
No RefEdit controls; I remember reading somewhere that they caused
problems. I guess I'll have to live with a message box that says
"Please use the Close button" for now.
Thanks for your time
Ken

On Apr 25, 1:14*pm, "Jim Cone" wrote:
Some things to try...
1. *Move Cancel =1 before Call CommandButton1_Click
2. *If you have an RefEdit control on the form in a frame - remove the frame.
3. *If you have a RefEdit control on the form (without a frame) remove the control (use an input
box).
--
Jim Cone
Portland, Oregon USA .http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)

"Ken"
wrote in ...



I have a userform for which I disable the close button on the caption
to force people to use my close button. *When I include a line to run
my close button in the following code I get an error when I close
Excel. *The error does not pop up when the form and file close, only
afterward when I close Excel. *Then I get Run Time Error 440,
Automation Error. *I only get the options to End or Help. *End clears
things up, Help is pretty useless.

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

If CloseMode = 0 Then
* *Call CommandButton1_Click
* *Cancel = 1
End If

End Sub

Originally the functionality in the CommandButton1_Click code was in
the UserForm_QueryClose() code. *I get the same result including it or
running it indirectly. *I do not get the Automation error when exiting
Excel when the code is run from the close command button. *This is all
the command button code is:


Private Sub CommandButton1_Click()

Dim x As Integer

'exit button

Unload Me

100

On Error GoTo 200

If Range("AutoClose").Value = "On" Then
* *ActiveWorkbook.Save
* *ActiveWorkbook.Close
End If

Exit Sub

200

x = MsgBox("can't save file right now. *Close without saving?",
vbYesNo)

If x = 6 Then
* *ActiveWorkbook.Saved = True
* *ActiveWorkbook.Close
* *Else
* *GoTo 100
End If

End Sub

This just closes the file if a flag is set to do so. *Sometimes the
network is slow and we get an error about the file being in use, so
the last part of the code deals with that.

I only get the automation error when closing Excel. *It only happens
when I try to use my command button 1 code from the
UserForm_QueryClose code. *Any ideas?

Thanks

Ken- Hide quoted text -

- Show quoted text -

"Ken" wrote in message
...
I have a userform for which I disable the close button on the caption
to force people to use my close button. When I include a line to run
my close button in the following code I get an error when I close
Excel. The error does not pop up when the form and file close, only
afterward when I close Excel. Then I get Run Time Error 440,
Automation Error. I only get the options to End or Help. End clears
things up, Help is pretty useless.

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

If CloseMode = 0 Then
Call CommandButton1_Click
Cancel = 1
End If

End Sub

Originally the functionality in the CommandButton1_Click code was in
the UserForm_QueryClose() code. I get the same result including it or
running it indirectly. I do not get the Automation error when exiting
Excel when the code is run from the close command button. This is all
the command button code is:

Private Sub CommandButton1_Click()

Dim x As Integer

'exit button

Unload Me

100

On Error GoTo 200

If Range("AutoClose").Value = "On" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

Exit Sub

200

x = MsgBox("can't save file right now. Close without saving?",
vbYesNo)

If x = 6 Then
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Else
GoTo 100
End If

End Sub

This just closes the file if a flag is set to do so. Sometimes the
network is slow and we get an error about the file being in use, so
the last part of the code deals with that.

I only get the automation error when closing Excel. It only happens
when I try to use my command button 1 code from the
UserForm_QueryClose code. Any ideas?

Thanks

Ken


This is something of a shot in the dark ... you might try putting in a
delay instead of executing your close code from inside the
UserForm_QueryClose procedure. Jim Rech suggested trying delayed
execution to solve a problem I was having trying to execute a
ThisWorkbook.Close from inside some "Before_Click" event code; maybe
it'll help you.

Move your CommandButton1_Click procedure into a standard module (I'd
give it some different name) and change UserForm_QueryClose as follows:

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = 0 Then
Cancel = 1
Application.OnTime Now, "CommandButton1_Click"
End If

End Sub

Let us know if this helped you.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)