The code I'm going to refer to ultimately performs correctly, but results in
an unexpected caption display on application exit.

On CommandButton_Click two subroutines are called that alternate the
CommandButton Caption:

If Button.Caption = "On" Then
Call SubOn
Else
Call SubOff
End If

SubOn
Sheets("MySheet").Button.Caption = "Off"
MsgBox "Button.Caption = " & Sheets("MySheet").Button.Caption ' Confirm
Caption
End SubOn

SubOff
Sheets("MySheet").Button.Caption = "On"
MsgBox "Button.Caption = " & Sheets("MySheet").Button.Caption ' Confirm
Caption
End SubOff

The rountines above work without a problem: The caption alternates between
"On" and "Off" .

However, I want the button to revert to "On" at Exit. Therefore in the
"BeforeClose" event I placed the Call SubOff.

When I click on the button and it displays "Off", and then click the "X"
(Exit application), this is the sequence of caption displays:

Initially the Button dispays "Off"
On Clicking "X" - the Button displays "On" and the MsgBox confirms that the
Caption is indeed "On".
HERE COMES THE PROBLEM:
When I Click "Ok" on the MsgBox the Caption on the Button reverts back to
"Off" and the Excel message display's the standard SAVE message YES, NO,
CANCEL

If I click "CANCEL" the Caption returns to the correct "On" display. If I
click "Yes" the worksheet is saved and Excel exits while the Button is still
displaying "Off", however when I restart the worksheet, it correctly displays
"On" when it loads.

Apparently, the caption IS being set correctly to "On", but for some reason
it momentarily reverts to the "Off" display when the Excel Exit message
displays.

Does anyone have any suggestions as to why this is happening and how to have
the Button display the correct caption when the Excel Exit message is
displayed?

Thanks

I can;t reproduce your error, everything works fine for me. I put SubOn and
SubOff inside a module, put the click event on the mysheets code and under
workbook a before close like you state and everything works correctly.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"mickey" wrote:

The code I'm going to refer to ultimately performs correctly, but results in
an unexpected caption display on application exit.

On CommandButton_Click two subroutines are called that alternate the
CommandButton Caption:

If Button.Caption = "On" Then
Call SubOn
Else
Call SubOff
End If

SubOn
Sheets("MySheet").Button.Caption = "Off"
MsgBox "Button.Caption = " & Sheets("MySheet").Button.Caption ' Confirm
Caption
End SubOn

SubOff
Sheets("MySheet").Button.Caption = "On"
MsgBox "Button.Caption = " & Sheets("MySheet").Button.Caption ' Confirm
Caption
End SubOff

The rountines above work without a problem: The caption alternates between
"On" and "Off" .

However, I want the button to revert to "On" at Exit. Therefore in the
"BeforeClose" event I placed the Call SubOff.

When I click on the button and it displays "Off", and then click the "X"
(Exit application), this is the sequence of caption displays:

Initially the Button dispays "Off"
On Clicking "X" - the Button displays "On" and the MsgBox confirms that the
Caption is indeed "On".
HERE COMES THE PROBLEM:
When I Click "Ok" on the MsgBox the Caption on the Button reverts back to
"Off" and the Excel message display's the standard SAVE message YES, NO,
CANCEL

If I click "CANCEL" the Caption returns to the correct "On" display. If I
click "Yes" the worksheet is saved and Excel exits while the Button is still
displaying "Off", however when I restart the worksheet, it correctly displays
"On" when it loads.

Apparently, the caption IS being set correctly to "On", but for some reason
it momentarily reverts to the "Off" display when the Excel Exit message
displays.

Does anyone have any suggestions as to why this is happening and how to have
the Button display the correct caption when the Excel Exit message is
displayed?

Thanks