Results 1 to 8 of 8
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    deleting active button (2003 NL)

    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.
    Greetings,

    Patrick Schouten
    (The Netherlands)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting active button (2003 NL)

    Why delete the button in the first place? You can always re-use the same button again, but just change it's caption?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: deleting active button (2003 NL)

    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
    Greetings,

    Patrick Schouten
    (The Netherlands)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleting active button (2003 NL)

    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

  5. #5
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: deleting active button (2003 NL)

    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!)
    Greetings,

    Patrick Schouten
    (The Netherlands)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleting active button (2003 NL)

    Does it help if you place one or more lines with

    DoEvents

    in your code?

  7. #7
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: deleting active button (2003 NL)

    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?
    Greetings,

    Patrick Schouten
    (The Netherlands)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleting active button (2003 NL)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •