Change a CommandButton caption during a called routine from A to B then back to A

Hello Experts:
As written, I am stymied as to how to do the following:
Using a command button on a sheet to delete user 671 possible entries (to clear the user entries for reuse)  with the intent to
1) change the caption while the called macro runs and then
2) return the caption to its earlier text...
2a) when the called macro completes its routine, and/or
2b) when the called macro is killed (if the user hits the [Esc] key)
Here's what I have so far:
==================================
Private Sub CommandButton1_Click()
'Resets Scores to zero by erasing 671 cells on 176 rows
With CommandButton1
       If .Caption = "Click to Clear all ''X'' entries" Then
           .Caption = "macro is running & deleting ''X'' entries"
         ElseIf .Caption = "macro is running & deleting ''X'' entries" Then
          .Caption = "Click to Clear all ''X'' entries"
       End If
       Call clearXentries  
  End With
End Sub
========================================
Do I have to place the entire called macro within the CommandButton1_Click() routine or is there another way to get the caption to change during the clearXentries macro 5-minute runtime?
Thanks
Berry
Berry MetzgerLean process improvement consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Hi,

I think you might just be missing a DoEvents which will update the button caption.  Also, make sure that screen updating is not off.  If you do turn it off, you will have to toggle it on and off again in the ToggleCaptionn sub.  See my example below...

Option Explicit

Private Sub CommandButton1_Click()
    'Resets Scores to zero by erasing 671 cells on 176 rows
    With CommandButton1
        ToggleCaption (True)
        Call clearxentries
        ToggleCaption (False)
    End With
End Sub

Private Sub ToggleCaption(ByVal IsRunning As Boolean)
    'Application.ScreenUpdating = True
    If IsRunning Then
        CommandButton1.Caption = "macro is running & deleting ''X'' entries"
    Else
        CommandButton1.Caption = "Click to Clear all ''X'' entries"
    End If
    'Application.ScreenUpdating = False
    DoEvents
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Berry MetzgerLean process improvement consultantAuthor Commented:
Thanks for your solution! It works, as I needed it to, with no modification required.  
Berry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.