Commandbar will not refresh while macro is running

[shelfish]

Good Monday all!

Dilemma d'jour: While running a macro, I need it to pause and allow
the user to make some changes to the spreadsheet....

************************************************************************
Sub waitForComments()

Dim btnRESUME As CommandBarControl

On Error Resume Next

'ADD BUTTON TO EXISTING CUSTOM MENU THAT ALLOWS USER TO RESUME MACRO
Set btnRESUME = Application.CommandBars("Worksheet Menu
Bar").Controls("My Custom Menu").Controls.Add(Type:=msoControlButton,
Temporary:=True)

With btnRESUME
.Caption = "RESUME"

'ANOTHER SUB SETS i = 1
.OnAction = "catchBtnRESUME"
End With

i = 0
Do Until i = 1
DoEvents
Loop

btnRESUME.Delete

On Error GoTo 0

End Sub
**************************************************************************************

The problem here is that, intermittently, the "My Custom Menu" is
missing completely. The 'Worksheet Menu Bar' still includes the
'charts' sub menu which must be the result of the fact that my
workbook has several charts and worksheets that it moves between. But
it will not update the menu bar as it does so. So the end user has no
way of clicking the "RESUME" button to continue through the macro.

Any thoughts on how I could force a refresh before starting the
DoEvents loop?

Many thanks in advance for the assistance.

S.

 

[shelfish]

....I also forgot to mention that if I hit ctrl+break to stop the
script, the menu refreshes and looks exactly as it should. If I hit
play again (resuming the DoEvent Loop), everything works fine.

S.

 

[Bob Phillips]

Try declaring btnRESUME as a module scope variable and use


Dim btnRESUME As CommandBarControl

Sub waitForComments()


'ADD BUTTON TO EXISTING CUSTOM MENU THAT ALLOWS USER TO RESUME MACRO
Application.OnTime Now() + Time(0, 0, 0), "AddControl"

With btnRESUME
.Caption = "RESUME"

'ANOTHER SUB SETS i = 1
.OnAction = "catchBtnRESUME"
End With

i = 0
Do Until i = 1
DoEvents
Loop

btnRESUME.Delete

On Error GoTo 0

End Sub

SubAddControl()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar") _
.Controls("My Custom Menu").Delete
On error goto 0

With Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "My Custom Menu"
End With
Set btnRESUME = Application.CommandBars("Worksheet Menu Bar") _
.Controls("My Custom Menu").Controls.Add(Type:=msoControlButton,
temporary:=True)
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[shelfish]

Unfortunately, that did not seem to resolve the issue. I did notice
that, while the loop was running, switching to another application or
the desktop and then switching back would resolve the issue. Then I
realized that a simple right-click worked....the "chart" control
disappeared and my "My Custom Menu" reappeared. The problem is still
pretty intermittent. Anyone else have any suggestions. This is for a
different end user and I don't want to have to acknowledge that random
right-clicks might be part of the SOP.

Thanks,
S.