Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Export a Toolbar (Excel 2000 SR1a)

    I have added some macros to a workbook to apply and remove certain filters. The macros have been added to two toolbars.

    If I copy the workbook to another PC, it will have the macros.

    How do I export the toolbars to the other PC please?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export a Toolbar (Excel 2000 SR1a)

    It all depends on what you want to do.

    Will the toolbars (and buttons) be on their computer constantly? (the macros will then need to be part of an addin or part of personal.xls).

    Should it only be available when this particular workbook is open? (and removed when the program is closed)

    Is is a completely new toolbar or do you want to add the buttons to a existing toolbar?

    Does it replace an existing toolbar? or replace any existing buttons (which should be "restored" when the workbook is closed)

    All of the above are possible in VB using:
    CommandBars.add [to add commandbar]
    controls.add(msocontrolbutton) [to add buttons]

    Look in help about these, think about the questions, and repost.
    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export a Toolbar (Excel 2000 SR1a)

    To see how to move your Toolbars to another PC, see THIS THREAD .

    However, it is usually best to have VBA code build the toolbar. If you use the procedure in the above thread, it will replace any toolbars that already exist on that machine, and that could upset the other users. If you build the toolbars in VBA code, they can only be available when your workbook is open, if that is what you want. If you want them available all of the time, then you would need to write the code to only run if the toolbar does not currently exist.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Export a Toolbar (Excel 2000 SR1a)

    Thank you both for your responses. I thought I wanted to copy the toolbar but Steve makes me think that perhaps I do not and require another solution. Also noting Lagare's reply, I certainly do not want to trash anything that may already be on the target PC.

    The option that I want is that the macros and the toolbars are only available with the particular workbook and that they automatically show / hide when the workbook is opened or closed on any PC.

    The macros that apply and remove the filters are in the workbook.

    The code to apply the "Home" filter is
    Private Sub HomeOn_Click()
    Selection.AutoFilter Field:=4, Criteria1:="Y"
    End Sub

    I created the two toolbars but they seem to be permanently available. If I run one of the macros from the toolbar when the workbook is closed, it opens it. I am more familiar with Word and I have not seen that behave in a similar way. As the toolbar is not part of the workbook (as it is displayed when the workbook is closed), I am lost as to how it opens the right workbook. However, my main objective is as described in the second paragraph.

    Looking at Steve's reply if I add the only the code

    Sub PGToolbar()
    CommandBars.Add FilterToolbar
    Controls.Add(msoControlButton)
    End Sub

    it errors on the Controls line which tells me that I've got the command bar line OK but the Controls line is looking for more. Presumably stuff about naming the control and the macro it is to run.

    I've tried unsuccessfully to track that down in the help but to be honest I'm not sure what I'm looking for. I'm reasonably OK with this sort of stuff in Word but I'm an Excel virgin! If you could just get me going with linking the Home filter macro to the button, I think I'll be OK.

    Thanks to both of you for your help.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export a Toolbar (Excel 2000 SR1a)

    You are in luck. I attached a file that might help.
    It has 4 buttons:
    Remove / restore all the toolbars
    Replace / restore the Menu bar
    Add /remove a new Submenu onto the Excel menubar
    Add / Remove a custom toolbar

    Check out the code to get an idea of how to setup what you want.

    Steve
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export a Toolbar (Excel 2000 SR1a)

    Though I didn't post the question, I certainly appreciate the way you responded. I found the examples very informative.
    I learned something. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Export a Toolbar (Excel 2000 SR1a)

    I downloaded your file a few minutes ago and have had a look around. I think I can see how to achieve what I want but it''s late at night here so I am going to defer until the morning.
    Ricky posted between me downloading and coming back to let you know how I got on. I echo his thanks for the way you responded. There's nothing like a live example and you supplied much more than I asked for. Thanks so much.
    Peter

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

    Re: Export a Toolbar (Excel 2000 SR1a)

    One method is missing from these posts:

    After creating *or changing* the toolbar, you can attach the toolbar to your workbook:

    - Rightclick the toolbar, select 'customize'
    - Click 'Attach'
    - If the workbook already contains a toolbar by that name, delete it first by clicking on it on the righthand side and choosing Delete.
    - Select your toolbar (on the left) and press 'copy'
    - Save the workbook (optionally: save_as an add-in).

    Also, You have to include code that deletes the toolbar when your workbook or add-in is closed, so that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old one. You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

    Private Sub Workbook_beforeClose()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub

    Once your file is loaded, XL automatically checks to see if another toolbar by the same name is present. If not, it copies the toolbar from your file to the user's system.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Export a Toolbar (Excel 2000 SR1a)

    Quite a thread I started here!!!!
    This post is to Steve and Jan. Thanks to both of you for this information.

    STEVE

    Now we're making progress.

    After looking at the various options, I decided to go for a UserForm. It has labels for each of the filters and toggle buttons to apply or remove each one. I've got a few set up and it's working fine. With the form set to non modal, as you click each toggle, the filter is applied or removed so you can make multiple selections.

    I have got the form to display automatically when the workbook is opened although the toggles do not reflect the saved filters. I don't need to save the applied filters so rather than set up variables, I am going to have a close routine that removes them all.

    At the moment, I have a macro to redisplay the form at any time using a keyboard shortcut. The only trick I can't see in your code is what displays the four buttons when you open the workbook and how they link to the code. The code itself I can see. I'd like to have a button that allows users to Show Filters Form and Hide Filters Form.

    Your code has been invaluable and will soon be on CD, so much safer!

    JAN

    Thanks for covering this. I did see that option but I wasn't certain that it was what I what I wanted and didn't have access to a second PC until late in the day to test it. It does copy the toolbar into the list available on the second PC which was my original objective. However, by then I had moved towards Steve's method of building the toolbar in VBA. As you will see above, I have finally settled on a userform.

    The bit about deleting the toolbar was useful and had I been aware of that earlier and known exactly how "attach" worked, I would probably have kept my toolbars, allowed the user to open the workbook thereby adding the toolbars to their list and then deleted them on closing. However, the method I have now gone for takes up less screen space so there is a payback for the extra work.

    Your response will be useful on another occasion.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export a Toolbar (Excel 2000 SR1a)

    The buttons in my spreadsheet are command buttons created using the control toolbox.
    The code for the clicking is on the WORKSHEET events NOT in the Modules.
    In VB dbl-click on the "Buttons" sheet to see the code. It is triggered by the "click" event.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Export a Toolbar (Excel 2000 SR1a)

    You'll be relieved to know its all working fine. Thought I better start with that bit [img]/forums/images/smilies/ohmy.gif[/img])

    Finding the code in VB wasn't the problem. It was creating the command buttons. After your last post I still couldn't get it until I tumbled that there is a Control toolbox in Excel as well as in VB. Once I found the Excel toolbox there were a couple of hiccups but then I got on top of it.
    Added a coloured button in row one which is part of the frozen pane as this makes it permanently visible.

    Everything now working exactly as I want. A final and very sincere thank you for bearing with me. Much appreciated.

Posting Permissions

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