Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom stuff (2000 sr-1)

    Hello excel wizards

    My question is (I think) quite involved so no more wasting time

    How do I customize excel to do what I want it to do.
    For instance, i tried making that personal.xls file with some code that I attached buttons to and put it into the xlstart directory but I don't like how it opens up this personal file to begin with. That means that any joe can open it, fiddle around and then save it which would be bad. What about the XLA addin feature, how do you make one? Do you have to code something in vba then save it as a xla file and then add it in? I want to make something sort of like what ASAP-UTILITIES does. It is a add-in easily downloaded somewhere on the web with a ton of awesome features for excel. I like how when you add it in it creates a menu bar at the top with all the various functions inside it, how can I make something like this? How does excel know to open it everytime I run excel?

    I know this question is rather involved but any help will be appreciated

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

    Re: custom stuff (2000 sr-1)

    First, the best way to get a Personal.xls file is not to create it yourself. If you have one that you created, then rename it and remove it from your XLStart directory. Then record a macro and tell Excel to put it into your personal macro book. Excel will create a personal.xls file in your XLStart directory that is hidden and that won't be seen from the worksheet. Now, open your old personal.xls (under its new name), and in the VBE copy your macros and paste them into the Personal.xls that Excel created.

    You can also convert a workbook that contains Functions to a .xla file. From the workbook, not in VBE, do a File SaveAs. In the "Save as type" drop down list select .xla (it the last entry in the list in XL 97). Give it a name and select a directory. Then from a workbook again, not from VBE, select Addins from the Tools menu. Click on the Browse. Find your .xla, select it, and click OK. Make sure the file is checked in the list box and click OK again. The functions in the .XLA file should now be available to use in any worksheet on that system. Excel opens all of the .xla files that are checked in the list every time it starts.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    Thanks legare, what do you mena by "Then record a macro and tell Excel to put it into your personal macro book." How do I tell it to do this? I have deleted the one i made since i was just playing around anyway. but I would definatly like to make it work like that

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

    Re: custom stuff (2000 sr-1)

    Open a new workbook and go to the Tools menu, then select Macro and "Record new macro" from the pop-up menus. In the dialog box, in the drop down list labeled "Store macro in" select "Personal macro workbook." Click OK and do something on the spreadsheet, then click the stop recording button on the little toolbar that appeard on the screen. Now, go to VBE and you should see a Personal.xls in the project browser that contains a module that contains the macro you just recorded.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    thanks legare that works perfectly, once again you have come to the rescue

    new question now, how do make it so everytime I open up a workbook a menubar shows up with custom functions that I design. I want to make it kind of like an add in so if the add in is working then menubar shows up and if the add in is not "on" then the menu bar is off. King of like the asap-utilities add in works

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

    Re: custom stuff (2000 sr-1)

    You have to write code in the addin Workbook Open event routine to build your menu and code in the Workbook Close event to remove your menu.
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    Hi Guys

    I've been reading this thread with interest as I would like to do something similar. I tried saving a macro to the personal.xls and then putting that macro on the toolbar, but when I closed Excel and opened it again, the button vanished.

    I must admit I use Word more than XL (v97, by the way), but in Word I have a template in my startup directory which is automatically open whenever Word is and the menus, toolbars, etc from that are always available - and this is what I would like to do in XL. I'm afraid I couldn't quite work out how to do it from the comments so far, although from the wording I think if I could work it out it would do what I want to do ... <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Hope that makes sense! Any help would be greatly appreciated.
    Beryl M


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

    Re: custom stuff (2000 sr-1)

    You must have a Personal.xls and it must be in your XLStart directory to make the Functions (not Subs) stored there show up in the UDF list. See Here for the best way to create Personal.xls.
    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    Thanks Legare, I had read that post but it was one of the ones that sounded like they should work but didn't when I tried them!

    A couple more queries - (1) you emphasize 'functions' not 'subs' - what is the difference, pls? I know I'm showing my ignorance here, but ...! and (2) the bit that wouldn't show up when I reopened XL was the button on the toolbar to run the macro, not the macro itself - does this need to be on a separate custom toolbar, perhaps? I think I had just added it to the standard ones.

    Many thanks for your time!
    Beryl M


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

    Re: custom stuff (2000 sr-1)

    The differences between a Sub and a Function are that a Function returns a value (which makes it usable in a formula in a cell), and a Sub does not (which means that it can not be used in a formula).

    Don't know why the button didn't hang around, and I am just leaving for a trip and won't have time to persue it for few days. There was another thread on this subject a few days ago that you might want to study. If that does not help, maybe Andrew or someone else can jump in and try to help.
    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: custom stuff (2000 sr-1)

    Now that the macro is in ...XLSTARTPERSONAL.XLS it may be matter of reattaching it to your selected button, closing Excel and telling Excel "Yes" when it asks if you want to save changes to your Personal.xls. Toolbar buttons are saved in a separate *.xlb file; buttons can be added to Excel's built-in toolbars manually or via code, but as Legare notes custom toolbars must be created via code. Once a button has been injected to any built-in Excel Toolbar it will stick. See the last paragraph of my post in for instructions on manually attaching a button to a standard toolbar, and <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=60552>this thread for a discussion on customizing other menus.

    Here's some code I wrote, with major help from lounger Brooke, to add a custom toolbar and buttons (try not to laugh, I deal with some really dumb users). The subs called by .OnAction are not included. This may not be the cleanest code, I'm just learning this stuff myself. In this case the code is loaded by and for a specific workbook which is NOT Personal.xls; but conceptually it should transfer.

    Sub custToolBar()
    ' called by Workbook_Open event
    Dim tempFETB As CommandBar
    For Each tempFETB In CommandBars ' kill it if it's already there
    If tempFETB.Name = "Field Tool" Then Application.CommandBars("Field Tool").Delete
    Next tempFETB
    ' create the toolbar
    Set tempFETB = Application.CommandBars.Add("Field Tool", 3, 0, 1)
    tempFETB.Protection = msoBarNoCustomize + msoBarNoMove
    ' create three buttons
    With tempFETB.Controls.Add(1)
    .Style = msoButtonIconAndCaption
    .FaceId = 41
    .OnAction = "SelectInputSheet"
    .Caption = "Select Input Sheet"
    .TooltipText = "Select Input Sheet"
    If ActiveSheet.Name = "Instructions" Then
    .Enabled = True
    Else: .Enabled = False
    End If
    End With
    With tempFETB.Controls.Add(1)
    .Style = msoButtonIconAndCaption
    .FaceId = 39
    .OnAction = "SelectInstructionSheet"
    .Caption = "Select Instruction Sheet"
    .TooltipText = "Select Instruction Sheet"
    If ActiveSheet.Name = "Input" Then
    .Enabled = True
    Else: .Enabled = False
    End If
    End With
    With tempFETB.Controls.Add(1, 2188)
    .Style = msoButtonIconAndCaption
    .BeginGroup = True
    .Caption = "E-Mail to <name>"
    .TooltipText = "E-Mail to <name>"
    .OnAction = "EMailSendButton_Click"
    .Enabled = True
    End With
    tempFETB.Visible = True
    End Sub

    Sub KillCustomToolBar()
    ' called by Workbook_Close event
    Application.CommandBars("Field Tool").Delete
    End Sub
    -John ... I float in liquid gardens
    UTC -7DS

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    Legare, hope you enjoyed your trip!

    Just out of curiosity, I tried again - wrote a macro, saved it in the personal.xls, put a button on the toolbar, assigned the macro, closed XL97 and when I opened it again, it had gone. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> Personal.xls is there, open and hidden, but the button is gone.

    The only thing I can think of that is different in this XL97 to the default is that both the main and alternate start up locations have been changed. Do you know where to specify in XL where it should look for the XLStart directory? Could that be it?
    Beryl M


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

    Re: custom stuff (2000 sr-1)

    As far as I know, you can't change the location of XLStart, at least without editing the Registry. In the Tools/Options on the General tab there is a place to specify the alternate startup directory.

    Try removing everything from XLStart and whatever the alternate points to and see if you still have the problem.
    Legare Coleman

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    Beryl

    You say the button had gone, what about the macro, is that still present in personal.xls?. If not, did you save personal.xls before you closed XL? You should get a prompt asking if you want to do this when you close XL.

    If the button is missing the problem is in your .xlb file (probably Beryl8.xlb as you are using XL 97). I suggest you go through your whole precedure again then use Explorer to see if your .xlb file was saved when you closed down XL.

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: custom stuff (2000 sr-1)

    Thanks, Michael - yes, the macro was still in personal.xls and it had saved it, but the button was gone. I haven't come across an .xlb files before though - what is this, pls, and where would I find it?
    Beryl M


Page 1 of 2 12 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
  •