Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Works Intermittently (Office 2003)

    I have a macro to toggle the TaskPaneController however it will only work after the task pane has been displayed once. If I open Excel by opening a specific file the task pane does not display (probably by design). If I run my macro I get a run-time error with the message "method 'Visible' of Object 'CommandBar' failed". However, if I manually display the task pane the first time, I can then use my macro to toggle the task pane for the rest of the session. The macro always fails on the second line so I tried reversing the False and True and it still failed on the Application.CommandBars("Task Pane")Visible = True. Can anyone see what is wrong with the code I am using?
    <pre>If Application.CommandBars("Task Pane").Visible = False Then
    Application.CommandBars("Task Pane").Visible = True
    Else
    Application.CommandBars("Task Pane").Visible = False
    End If</pre>


    Thanks
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Works Intermittently (Office 2003)

    I don't have Excel 2003 so I couldn't test the following. I use the following method to toggle toolbars.


    Application.CommandBars("Task Pane").Visible = not(Application.CommandBars("Task Pane").Visible)

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

    Re: Macro Works Intermittently (Office 2003)

    I can reproduce your problem. I don't know what causes it, but the following line works to display the task pane even if it wasn't shown previously:

    CommandBars("Worksheet Menu Bar").FindControl(ID:=5746, Recursive:=True).Execute

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Works Intermittently (Office 2003)

    That does it!! Thanks Hans.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Works Intermittently (Office 2003)

    This code does the same thing that my code does - works only after manually displaying the task pane. It must be a 2003 issue. Hans came up with code that works even if the task pane has not been displayed. Thanks, mbarron.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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