Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating/deleting a commandBar in Excel (Excel 2K sr1)

    Okay, I am close to going crazy. I'm attaching code here that I want to have in an Excel template (a template for an expense report).

    The macro (PrintActive) works fine when called from the Tools|Macros menu. I'm trying to set up the template so that each time the template is launched it creates a CommandBar called "Expense Toolbar" that has a button that calls that macro. I tried to do that in the "AddBar()" sub. I added the bar and then added the button to the bar.

    Then, I need the toolbar to be deleted when the worksheet is closed.

    Grr. This code is a combination of codes, some taken from other WOPR threads and some from a book I have (Microsoft Excel 2000 Power Programming with VBA0.

    Any thoughts would be appreciated...it is bothering me, tremendously, that I can't have a toolbar in a template the same way I can in Word!

    ---------------------------------------------
    Private Sub Workbook_Open()
    Call AddBar
    'calls the AddBar feature to add the toolbar
    End Sub
    -----------------------------------
    Private Sub WorkBook_BeforeClose(Cancel As Boolean)
    Call DeleteBar
    End Sub
    --------------------------------------------------------
    Private Sub DeleteBar()

    On Error Resume Next
    CommandBars("ExpenseToolbar").Delete

    End Sub
    --------------------------------------
    Private Sub AddBar()

    Dim Bar As CommandBar

    Set Bar = CommandBars.Add("ExpenseToolbar", msoBarTop)
    'this puts the toolbar above the workbook

    Set btn = CommandBars("ExpenseToolbar").Controls.Add(Type:=m soControlButton)
    With btn
    .OnAction = "PrintActive"
    .Style = msoButtonCaption
    .Caption = "Print Active Pages"
    End With

    With Bar
    .Visible = True
    .Protection = msoBarNoCustomize
    End With

    End Sub
    ------------------------------------------

    Sub PrintActive()

    Dim oSheet As Worksheet
    For Each oSheet In Worksheets

    If oSheet.Range("H39") > 0 Then oSheet.PrintOut

    Next oSheet
    End Sub
    ----------------------------------------

    All of this code is in the "ThisWorkBook" area of the VBAProject for my template.

    Thanks in advance...

    klyjen

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel 2K sr1)

    Putting TRUE as final .Add method parameter is supposed to take care of that.

    Set Bar = CommandBars.Add("ExpenseToolbar", msoBarTop,,True)
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel 2K sr1)

    Well, that did something...I modified the line per your instructions (you intended two commas there, right?) and now when I launch the template from File|New I get a rt error '91' Object variable or With block variable not set Error.

    When I click debug, it goes to the "set bar" line...

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel 2K sr1)

    Like I said, "it's supposed to." Beats me. I never relied on the temporary parameter myself.

    Why not do a delete method on that commandbar in the Worsheet_BeforeClose event...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel 2K sr1)

    I have the delete method set in there already...the problem is that it won't create the toolbar in the first place...

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    <pre></pre>

    If you haven't reset your target by using JimByTheBay's code, this rework of your code runs for me - as long as I put it in standard module and not the ThisWorkbook event.<pre>Option Explicit

    Private Sub Auto_Open()
    Call AddBar
    'calls the AddBar feature to add the toolbar
    End Sub

    Private Sub Auto_Close()
    Call DeleteBar
    End Sub

    Private Sub DeleteBar()
    On Error Resume Next
    CommandBars("ExpenseToolbar").Delete
    End Sub

    Private Sub AddBar()
    Dim Bar As CommandBar
    Set Bar = CommandBars.Add("ExpenseToolbar", msoBarTop)
    'this puts the toolbar above the workbook
    With Bar
    .Controls.Add(Type:=msoControlButton).Caption = "Print Active Pages"
    .Controls(1).OnAction = "PrintActive"
    .Controls(1).Style = msoButtonCaption
    .Visible = True
    .Protection = msoBarNoCustomize
    End With
    End Sub

    Sub PrintActive()
    Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    If oSheet.Range("H39") > 0 Then oSheet.PrintOut
    Next oSheet
    End Sub</pre>

    HTH
    Gre

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    unkamunka--

    Thanks for the thoughts. I tried your code, and it isn't working for me. I'm sort of at a loss; I can't figure out why this is not working. This file is saved as a .xlt, as I intend it to act as a template--could that be causing a problem?

    klyjen

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    jen - that DOES surprise me. It ran for me as a template on Excel 2K SR-1. Normally, I would suggest that it was something to do with macro settings, but that doesn't seem to be consistent with the content of your thread. Here's the template itself. HTH

    BTW Did you ever get to the bottom of your vanishing Outlook.OTM issue?
    Attached Files Attached Files
    Gre

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    No, it didn't work--I copied and pasted your code into a new module and deleted everything else. Now, I don't get any errors, which is a good thing--but I also don't get a toolbar.

    *sigh* This is very frustrating. I thought about macro problems, but my settings are at "Medium" and I have "installed" templates set to be trusted anyway. It's very weird.

    As for the Outlook issue--no, I still haven't resolved that. In fact, it's getting worse. I am getting close to shutting down the entire system and issuing IBM typewriters to all of the attorneys and secretaries here. With a calculator. There's nothing they need to do that can't be handled with a typewriter and a calculator...I'm convinced of it.

    *g*

    Jen

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    Just for the sake of testing, did you try loading the template I sent intact to your templates directory and going from there?

    For me, the code runs both when I open the template itself and when I create a new workbook based on the template.

    On the other issue, you may be developing "load" issues on your network - although that is starting to get beyond my area of expertise. In the meantime start gathering up all those old golfballs! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Gre

  11. #11
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    Yup--I put your file in the templates directory and started it--no toolbar.

    I am going NUTS.

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    Does the AddBar macro run from the VBE? Try changing the name of the Toolbar to something silly/unique - in case it's ambiguous.
    Gre

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    Hi,
    A couple of thoughts in addition to Unkamunka's idea of testing the addbar routine from the VBE. Have you tried adding a debug.print line or a msgbox to your AddBar routine to check it's running?
    You might also want to try having your workbook_open and workbook_close code in the thisworkbook module and your add and delete bar routines in a standard module.
    finally, it might be worth running Application.EnableEvents = true from the immediate window just to make sure that that hasn't been turned off at some point.
    If none of that works, you could add a bar.enabled = true line and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    Thanks rory.

    I switched the auto_open and auto_close to workbook_open and workbook_close, and then moved them into the ThisWorkbook object.

    Now, when I launch the template, I know that the AddBar routine is running, but I'm getting a "run time error '5': Invalid Procedure Call or argument" error on the

    Set Bar = commandBars.Add("ExpenseToolbar", msoBarTop)

    line. Or, at least, when I click "Debug", that is the line that is highlighted.

    Does that shed any light on anything?

    Unkamunka--I got the same result when I changed the name of the toolbar and the routine...{{edit to add:}} and no, the routine does not run from the VBE.

    I truly think I may be going nuts. Or, something else is causing this, something so obvious that I can't see it.

    jen

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating/deleting a commandBar in Excel (Excel

    Rory, the reason I moved the code out of the ThisWorkbook module is that - like klyjen (and Kevin?) - trying to Set the Bar variable with that approach Errored out. (I seem to remember this problem also cropping up on another thread.)
    Gre

Page 1 of 4 123 ... LastLast

Posting Permissions

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