1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2002 custom template

Discussion in 'Business Applications' started by FLYNNE, Nov 3, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    I have constructed a template which contains my own customised toolbars which are very different to the usual toolbars and saved as an .xlt file in :-
    Documents and Settings\user_name\Application Data\Microsoft \Excel\XLstart.
    However whenever I try to use this template I get the usual toolbars and not my customised set.
    This is causing me much consternation !!!.
    I would be most grateful for any suggestions as I am obviously doing something very wrong.
    Regards
    Flynne
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Do you mean they don't exist, ot they just don't display?
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Did you actually attach the toolbars to the file .. ?
     
  4. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    Dear XLGURU

    They certainly don't display and I cant any evidence of their existence

    Regards

    Flynn
     
  5. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    Dear Firefly

    I did not know you could do this.
    How can this be done.

    Regards
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    "Did you actually attach the toolbars to the file" would've been my next q.

    View -- Toolbars -- Customise -- Attach.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Didn't meant to step on your toes there, mate. My apologies. :)
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> My apologies.

    Not at all. I believe that experts would recommend using Workbook_Open code to build the bars on the fly (and Workbook_Close to dump them on exit). That's beyond my capabilities - all yours, FF.

    Rgds,
    Andy
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well to create a toolbar, you first need to click Tools -> Customize. This will bring up your Customize window and let you add/delete buttons and toolbars, etc. On the Toolbars tab, click New. This will pop up a new toolbar for you to populate with whatever buttons you'd like.

    Now you need to add buttons. Click on the Commands tab. From here you will be able to see all menu/toolbar buttons that Excel has (not necessarily all FaceID's, which are the pictures associated with a MenuItem). What I would suggest (and I'll give the default after) is to surf through them and find the icon that you like, don't worry about the text right now. Pop up *sub* menus should also be available here. All you need to do, when you find the one(s) you like, is to click and drag it onto your newly created menu. Do this for all the buttons you'd like. Once you've completed these parts you should have a menu with some buttons on it. Default method: In the Categories List Box, scroll down and select Macros; then on the right, click and drag the smiley face onto your new Toolbar. This is the default method usually described when creating a toolbar with custom buttons.

    From here, you can (whilst leaving the Customize dialog box open - always while editing a Toolbar/Menu) right click your buttons and do a variety of different things. I won't go into all of it, but you can change the text that appears next to the button(s), choose whether or not text should even appear with the button(s), assign specific macros to the button(s), physically change the button image (or choose from a small list of pre-defined images), etc. To take an item off of your new menu, simply click and drag it off of the menu to anywhere not on the toolbar, this will get rid of it.

    Now to assign it to a specific workbook (which is highly recommended as a standard practice when using workbook specific toolbars) click back to the Toolbars tab. If not already highlighted (should be by default) do so now. Then click 'Attach..'. With your workbook in question open (as you will be attaching it to the Active Workbook) pick your Toolbar from the list on the left, press the 'Copy >>' button and ensure it was populated in the list on the right, then click Ok. Please note that the List Box will let you make multiple selections of toolbars.

    This is the basics for creating toolbars and attaching it to a workbook. Now (assuming still that the toolbar created will be workbook specific) to do like Andy has suggested is also highly recommended. The code would go into your ThisWorkbook module of the workbook (example code pasted below) and basically make it visible upon open and invisible upon closure of the workbook. This won't delete it, but makes for a very nice dynamic for one to customize their workbook/application.


    To past code:
    1) From Excel, press Alt + F11. This will open the Visual Basic Editor (VBE).
    2) Find your workbook on the left (If no left frame exists, press Ctrl + R; name will be in bold and in parenthesis.
    3) If not already, expand 'Microsoft Excel Objects'.
    4) Double click on 'ThisWorkbook'.
    5) Paste code in right pane.


    Code...
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.CommandBars("Custom 1").Visible = False
    End Sub
    
    Private Sub Workbook_Open()
        Application.CommandBars("Custom 1").Visible = True
    End Sub
    
    Just make sure that you change the "Custom 1" to the name you have assigned your toolbar.
     
  10. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    To Firefly and XLGuru

    Many thanks to your swift responses to my question.
    I will try out all recommendations and come back to you in a few days.

    Regards

    Flynn
     
  11. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    Hullo FF
    Your suggestions work just fine- Thank you.
    May I ask another questioon
    Is it possible to construct an excel file which contains toolbars unique to that file i.e do not exist at all in any other file - not even in the list of toolbars.

    Regards

    Flynne



    Is it possible to construct an excel file which contains toolbars unique to that file i.e do not exist at all in any other file - not even in the list of toolbars.
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yes you can do that. But it would require creating it every time you opened the workbook. This would be *cleaner* but slow your opening process slightly. As it's with the workbook_open still, it will be faster than running it as a regular sub, and would be faster still if you made it as an add-in (which sounds like the way you may want to go, and I highly recommend also, very cool stuff).

    Use this in your ThisWorkbook module ...


    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        DeleteMenuPlease
    End Sub
    
    Private Sub Workbook_Open()
        CreateMenuPlease
    End Sub
    
    Then in a standard module, you'd use something like this ..


    Code:
    Option Explicit
    Sub CreateMenuPlease()
        Dim myMenu As Object, cb As CommandBarButton
        On Error Resume Next 'just in case the menu is already deleted
        DeleteMenuPlease 'call this so we don't replicate it every time
        Set myMenu = Application.CommandBars.Add(Name:="Custom 1")
        myMenu.Visible = True
        With myMenu
            .Position = msoBarRight
            '** First Item on Toolbar
            Set cb = .Controls.Add(Type:=msoControlButton, _
                Before:=1, Temporary:=True)
            cb.Caption = "Macro 1"
            cb.FaceId = 111
            '** Second Item on Toolbar
            Set cb = .Controls.Add(Type:=msoControlButton, _
                Before:=2, Temporary:=True)
            cb.Caption = "Macro 2"
            cb.FaceId = 1549
            '** Thhird Item on Toolbar
            Set cb = .Controls.Add(Type:=msoControlButton, _
                Before:=3, Temporary:=True)
            cb.Caption = "Macro 3"
            cb.FaceId = 1000
            '** Fourth Item on Toolbar
            Set cb = .Controls.Add(Type:=msoControlButton, _
                Before:=4, Temporary:=True)
            cb.Caption = "Macro 4"
            cb.FaceId = 800
        End With
        Set cb = Nothing
        Set myMenu = Nothing
    End Sub
    
    Sub DeleteMenuPlease()
        Application.CommandBars("Custom 1").Delete
    End Sub
    
    There are quite a few personal settings in this latter piece of code that you can adjust. Probably the most visible (from Excel) is the .Position property. You can set this to Top, Bottom, Right, Left; I personally like the right for a custom job like this, as traditionally there is nothing else that goes there, it's discreet yet prominent.

    The FaceId's are obviously the pictures associated with them, if you'd like to change it, there are thousands of FaceId's to choose from. One word of caution if going across different windows versions, the FaceId values do change when going from 2000 and up. Some values in 2000 are not the same as 2002 (XP), so just be aware of that fact, although the majority are the same.



    HTH
     
  13. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    Hullo Firefly

    I have followed your recommendations above and they work fine .
    However is it possible to use vba code to assign macros to the custom toolbar buttons
    created ?
     
  14. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Try chunks like these after each "cb.FaceId" line:

    With cb
    .OnAction = "Macro_1"
    End With

    & so on.

    HTH,
    Andy
     
  15. FLYNNE

    FLYNNE Thread Starter

    Joined:
    Nov 13, 2002
    Messages:
    243
    To XL Guru

    Again thank you for your quick and informative answer.
    There is some other things to ask - I have assigned various macros to the buttons created in my custom toolbar by the OnAction method suggested by you which works vey well.
    However I need to know the vba code to assign to and display text on the button on the custom toolbar. This is equivalent to Tools , customise ,right click on toolbar button
    and click on the Text (always) option .
    You and FireFly have been most helpful in answering my questions.
    However if I knew how to use the help section in Excel Visual Basic for Applications more efficiently I may be able to find the answers for myself.
    Any tips you could give me on this matter.

    Regards

    Flynne
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/292025

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice