deleting active button (2003 NL)

Topic

New Reply

Hello all,
I have a macro that is removing all menus and toolbars from the Excelworkbook and is creating three new toolbars.
One of the toolbars I use for navigating through the sheets. So if you click a button, the current sheet is hidden and the new sheet is shown, and the toolbar gets new buttons te navigate further.
The problem is that it is not possible (for now) to remove all buttons from that toolbar, because this action is activated by a button from that same toolbar. And it is this button that is not possible to delete.
Is it possible just before deleting this buttons to deactivate that button so I can delete it.

I use the following code:
For Each cbCtrl In Application.CommandBars(“cbNavigate”).Controls
cbCtrl.Delete
Next

Thanks in advance for your reaction.

Reply | Quote

Replies

Why delete the button in the first place? You can always re-use the same button again, but just change it’s caption?

Reply | Quote

Hello Jan Karel,

I delete it just to keep track of all the buttons, faces and macro’s
The sheets have different buttons to navigate to different sheets. So I have setup a case list to create the correct captions, faces and actions for every sheet

Reply | Quote

Put the code to delete the toolbar buttons in a separate macro and call that from the macro associated with the toolbar button using Application.OnTime:

1) Toolbar button calls ButtonClick:
2) ButtonClick in its turn calls DeleteButtons, but indirectly, through OnTime.

Sub ButtonClick()
Application.OnTime Now + TimeSerial(0, 0, 1), “DeleteButtons”
End Sub

Sub DeleteButtons()
Dim cbCtrl As CommandBarControl
For Each cbCtrl In Application.CommandBars(“cbNavigate”).Controls
cbCtrl.Delete
Next cbCtrl
End Sub

Reply | Quote

Hello Hans,
This seems to work, but when recreating the toolbar, it is not showing the buttons until I hit a key on the keyboard or click with the mous in the sheet.
Any idea?

(mmm this not always the case!)

Reply | Quote

Does it help if you place one or more lines with

DoEvents

in your code?

Reply | Quote

Hans,

I have never worked with DoEvents.
Could you explane a bit about this function?
And could you explane why the code is working now with the OnTime function?

Reply | Quote

DoEvents gives the CPU a bit of time to process pending events such as repainting the screen.

Application.OnTime starts a timer that will execute another macro at the specified time. The macro that contains Application.OnTime then continues and finishes normally. So when the timer goes off and executes the other macro, the button is no longer “active” and can be deleted normally.

Click in DoEvents or in OnTime and press F1 to get the built-in help for these instructions.

Reply | Quote