Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Button Recommendation (XP; SR3)

    I am looking for advice on the best practice to disseminate code to about twelve Excel Users.

    What I envision is a "custom button" on their toolbar which references the code. I don't particularly want to add the code to each of their "personal.xls" files. Is it possible to place the code in a file on a shared drive? I have seen some threads on XLA files but have never used this feature.

    Thanks,
    John

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Button Recommendation (XP; SR3)

    I would create an add-in.

    See Creating An Add-in From An Excel Macro by Jan Karel Pieterse

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Button Recommendation (XP; SR3)

    Tony,

    Thank you,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Button Recommendation (XP; SR3)

    Tony,

    I downloaded Jan's utility file "Setup YourAddin 001.xls" which enables one to install the addin quite easily. Within the Excel file there is a cell for a path. I am interpreting this to be the path where I saved the XLA file to. In this case, a share on the network. When I clicked on a button to install, I receive a message box indicating that it is being installed "to" not "from" the path.

    I decided not to install until I better understand what truly is happening.

    Your advice is appreciated.
    John

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

    Re: Custom Button Recommendation (XP; SR3)

    The idea is:
    - Save Jan Karel's setup utility 'Setup YourAddin 001.xls' in a convenient location
    - Rename it to suit the name and version of your add-in.
    - Specify where your add-in will be installed in Sheet2!B4. The default is the add-in path for the current user. You can also select the All Users add-in path, or type a path yourself.
    - Save your add-in in the same location as the setup utility, NOT in the path specified in Sheet2!B4.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Button Recommendation (XP; SR3)

    Hans,

    Thanks for the clarification. I was able to successfully install the XLA addin that I created.

    The variable "vReply" in Jan Karel's utility file is not declared because of the "Option Explicit" statement. So if others are interested in using it, simply declare the variable as a string.

    Regards,
    John

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

    Re: Custom Button Recommendation (XP; SR3)

    I'm not sure which of Jan Karl's routines you are talking about, but if it is the Addin Setup Utility that I am looking at, the vReply variable is defined as a Variant in the statement immediately after the Option Explicit statement. This variable should NOT be defined as a String! It should be defined as a Variant. I haven't looked at all of the code in detail, but you also might be able to define it as an Integer, bit it should not be definde as a String.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Button Recommendation (XP; SR3)

    Thanks Legare.

    One more question... Can an XLA file be opened for viewing? I know one can protect the VBAProject with a password and include a Option Private Module within a module.

    Regards,
    John

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

    Re: Custom Button Recommendation (XP; SR3)

    AFAIK, a .xla can not be opened for viewing.
    Legare Coleman

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

    Re: Custom Button Recommendation (XP; SR3)

    You can open the xla for viewing, just open the VBE, clcik on the Thisworkbook object in the project explorer and chage the IsAddIn property to false. Of course you cannot do this once the addin's code is locked from viewing and you haven't provided the PW.

    Indeed does my setup utility expect the addin to be in the same location as the setup file. But that may be easily changed to a fixed location if you need that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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