Installing an Add-In in Excel 2007

In Installing an Excel Add-In I described the protocol for installing an add-in in Excel. The protocol I described was valid for Excel 97 through 2003, but the change in user interface which came with Excel 2007 was accompanied by a more tortuous protocol. It’s not really more complicated once you get used to it, but the first few times it’s an adventure.

To install an Excel 2007 add-in, click on the Office Button, the big round decoration in the top left of the Excel window.

Office Button

This opens the Office Menu. Click the Excel Options button at the bottom of this menu.

Office Menu

The Excel Options dialog opens up. Click the Add-Ins item in the list along the left edge of the dialog to see the Add-Ins panel.

Excel Options: Add-Ins

Make sure the Manage dropdown at the bottom shows Excel Add-Ins, then press the Go button. Finally this brings up the familiar Add-Ins dialog.

Excel Add-Ins Dialog

If the add-in has been stored in one of the default add-in directories, it will appear in the list. Check the box in front of a listed add-in to install it, or uncheck the box to uninstall it. If the add-in does not appear in the list, click Browse, and use the Browse dialog to locate the add-in file.

This long protocol can be shortened in a couple of ways. One way is to add the Add-Ins command to the Quick Access Toolbar (QAT). Click the little dropdown button to the right of the QAT, and choose More Commands.

Customize QAT

Click on the left hand dropdown, and choose All Commands.

Customize QAT

Scroll down, select Add-Ins, and click the Add button.

Customize QAT

Press OK and the Add-Ins button appears on the QAT. It doesn’t look like much, just a greenish round button, but if you mouse over it, you can see the Add-Ins tooltip.

Customized QAT

If you remember the Excel 2003 accelerator keys, you know an easy way to open the dialog. Excel 2007 honors Excel 2003′s Alt-key menu shortcuts. In Excel 2003, you would hold Alt and press T for the Tools menu, then I for the Add-Ins command. In Excel 2007, therefore, you can hold Alt and press T then I (Alt-T-I) to quickly open this dialog.

Peltier Tech Chart Utility

Comments

  1. Jon, what this that Excel icon that is sitting just to the left of the Home tab? This is the first time that I can recall seeing that.

    While I’m here, what are those three add-ins that you have obscured in the picture. I’m guessing some kind of top-secret “Excel 2003 Charting Tools” add-in that you are developing to get around the new charting engine. :-)

  2. If you have the Show All Windows in Taskbar option unchecked (Office menu > Excel Options > Advanced > Display), this icon appears when a workbook is open and maximized. It essentially serves as the icon on the top left of the workbook’s title bar when the workbook window is maximized.

    IMO the Show All Windows in Taskbar option should always be turned off. Why clutter the taskbar with a dozen Excel icons? One is enough.

    And the obscured items in the add-ins list are utilities I’ve developed for clients. There’s nothing else really top secret in the works. Enhancements to existing utilities,which so far are still free, and one or two new things, including a really cool error bar utility that gets around how bad the Excel 2007 UI is for adding them (and it also improves a bit on Excel 2003).

  3. Interesting. I hate not having all windows showing in the Taskbar, so I leave that option checked. Typically, I will have at most 3 workbooks opened at once and I tend not to run more than 3 or 4 applications at a time. So, it works for me. I can see the advantage when you have lots of workbooks open.

  4. “Excel 2007 honors Excel 2003’s Alt-key menu shortcuts”

    Well, most of them.
    Some are broken in all Office 2007 suite, not just Excel.
    Annoyingly, ALT-F-V (print preview) is gone, and ALT-F-I goes to options instead of document properties. I use these two a lot and keep getting the wrong thing. Grrr…
    Must retrain fingers to do strange contortions (ALT-F-W-V, or F-E-P respectively)

  5. thanks!

  6. Can I get the ability do make control charts with an add in or do I have to have SPC Excel?

  7. SPC For Excel has much broader capabilities than simply making control charts. It handles a wide range of statistical processing.

    However, for making simple control charts, you can use plain old Excel line charts, as I’ve descibed on my web site in Run Chart with Mean and Standard Deviation Lines and on this blog in Introducing Control Charts (Run Charts) and Use a Chart to See Patterns in Your Data.

  8. Jon, do you have any information on deploying com addin’s? I have several com addin’s created in Visual Studio 2K5. I’ve created a setup project and installed it on an XP test machine. The setup includes the necessary registry entries and the addin appears in the Com Addin’s list in Excel, but I cannot activate them. I can select them, but after selecting they are not available and reviewing the addins again still shows them to be inactive.

    Thanks.

    Ross

  9. Ross -

    I have no experience with creating and deploying COM add-ins. My first steps would be to Google for COM add-ins and VS2005, and see where that gets me.

  10. This looks pretty straightforward. Will give it a go. BTW – following the Screen shots is really easy .

  11. A screen shot is worth a thousand words.

  12. thanks a lot..it helped a lot!.

  13. Thank you so much! I am so grateful for this information…saved me oodles of time while working toward a project deadline!

  14. G’day John!

    Do you have any idea how to add more commands to the Excel 2007 Ribbon. I wanna add a hyperlink to a file.

    Cheers
    Mak

  15. Mak -

    Well, there’s that sad little excuse for a toolbar, called the QAT, stashed up in the title bar. You could try adding your command there. Otherwise you could hack together some XML, which isn’t too hard, but there’s a bit of a learning curve.

  16. Thanks for the info, Jon.

    I got another problem I believe.
    I wanna merge columns I to M into N, but there are some date and time formats. The excel merges the columns but wont preserve the date and time format. Can you plz help me fixing it?

    I J K L M FORMULA
    2009-5-14 T 00:00:00 + 00:00:00 39947T0+0

    FORMULA at N7=(I7&””&J7&””&K7&””&L7&””&M7)

    Thanks,
    Mak

  17. OMG – I got thet just sorted out from a forum thread on Microsoft.

    I needa use TEXT formula to do the things.

    Thanks anyways.

  18. The SEND button is a wonderful teacher, isn’t it?

  19. After 30 minutes of trying various options in the new interface your page has just saved me looking silly in the face of a delegate’s question! Hehe. Many thanks.

  20. I can’t say how much this shortcut add-in has helped optimize my data analysis — in my field error bars are a necessity. Things were so much easier in Office 2003 – but this shortcut makes adapting to Office 2007 SO much easier for me.

    Thanks for taking the time, I truly appreciate your work.

    ajp

  21. Hi there,
    Does anyone know how to add-in ASAP utilities as one of the tab on the tool bar? When I first downloaded it, it showed up on a blank worksheet. After I close this blank worksheet, it just disappeared. It shows up in the add-in, but when I clicked and “OK” it didn’t do anything. :( ;(

  22. Kim – Did you run the setup.exe file? This should install all the commands in the appropriate tab/menu configuration.

  23. Hi Jon,
    Thanks so much for your advice. I called my IT guy, don’t know what he did but he fixed it.

    While waiting for my IT guy, I found a different approach for sort by color in Exel 07.

    What do you think of the Custom sort/Sort by cell color?

  24. Kim – I know Excel 2007 introduced the ability to sort by various formatting, but I haven’t used it yet.

  25. Oh yeah, Jon, I had no ideas how easy it is to use Custom Sort/Sort by Cell color in Excel 2007. For 200, I guess there’s no other way around it but using ASAP Utilities or similar custom function.

  26. I’m having a problem downloading an Add-In for Excel. I put my disc in and click on the Add-In, it downloads to Programs but it is not in the upper left hand corner of my Excel spreadsheet when I open it. I have even done a search for it and it says it isn’t anywhere on my computer. I have even looked in the Add-Ins. What am I doing wrong?

  27. Teresa -

    What is the add-in? What is the name of the file in Programs? Is it an exe file that needs to be executed?

  28. Thanks so much for the error bar add-in for Excel 2007! I really appreciate it – it was really frustrating before and now you are saving me so much time when I am making graphs for my research and school. Your add-in works great. Thanks, Jon!

  29. thank you:)

  30. Hi Jon, I’m having a problem with the add in. I followed all of the instructions to install the file and when I open Excel it shows that each time the add on savedbf is checked in the add on list, but I don’t understand how to actually use the function of the add-in. I had assumed it was going to add a option in Save as that said Save as file type .dbf or something of that sort but that wasn’t amongst the list of choices. And there is a file save as type Excel addin or Excel 2003-97 add in type but from what I gather thats to actually save a add in.

    So is there some kind of button to actually convert the file to a dbf file that i have to add to the tool bar. I’m new to using add-ins with excel because 2003 has always met my needs. Any advice would be greatly appreciated!

    Thanks!

  31. Tyler -

    I’ve never heard of that add-in. You have to contact the producers of the software to see how it’s supposed to work.

  32. Jon, I have created an Add-in, but the Add-in works on the basis of the database that is stored on the worksheet. So its essential that the worksheet containing the database is always open.
    I would like to forward this as a stand-alone add-in, how do I create this as an add-in and have the database accompany it.
    Currently I have seen that if I save it as a .xlam file and install it as add-in the Tab Buttons and macros are made available, but the database does not get installed.
    Any advice here?

  33. Chris -

    Instead of making a separate add-in, you could put the code into the workbook that contains the database.

    Alternatively, you could disable the buttons and macros if the database is not open, and show a message reminding the user to open the database.

  34. Hi Jon, thank you for this extremely easy to follow instructions! However, when I go through the steps, the Add-In called “Analysis ToolPak” always appears in the list of “Inactive Application AddIns” (3rd figure above). I have restarted the Excel several times, but to no avail. When I look at the checklist of AddIns, “Analysis ToolPak” is checked as in Fig 4 above. What am I missing?

    More importantly, I cannot plot frequency chart which I thought I’d be able to do when I installed this AddIn.

  35. Nice that you create your own ribbons in 2010 for this kind of thing

  36. Angus -

    Strange behavior. I just tested on my computer and it works as expected. What if you uncheck Analysis Toolpak in the list of addins, restart Excel, then check it again?

  37. How do you install an add-in? I went through all the steps, but do not see this add-in being shown in the Add-Ins tab. After installing, do I need to shut down Excel and restart?

  38. Sonja -

    When you browse and select an add-in, it should then appear on the list, with a checked box in front of it. Did this happen?

    There is no need to restart Excel to use an installed add-in. If Excel does not close normally, though, it doesn’t remember any add-ins that were installed during that session.

  39. I am trying to create an addin for distribution in Excel 2007 within my company. I have a spreadsheet with VBA code that works very well. The procedures reformat data after a paste into excel from MSSQL in a certain way that my team would like to use in their work. I don’t think the routines themselves are my problem since they work fine in the .xlsm file and I can assign hot keys etc. But when I save the file as an .xlam file and ensure that the addin is available, none of the functions or sub routines are available. What I am doing wrong? I can see all the code when I press Alt-F11. Excel just won’t show them to me under Macros or on the Add-In tab.

  40. Can you run the code from the VBE?

  41. Yes – everything seems fine from VBE.

    Let me give you the simplest example of my misunderstanding. I have a macro:

    Sub MyMacro()
    MsgBox (“Hello”)
    End Sub

    This macro is located in the “ThisWorkbook” object of the “MyMacro.XLSM” file in the VBE. In Excel I can see the “MyMacro” macro in the Macros list under the View tab when I have this spreadsheet open. I can assign a hotkey to it like Ctl+j.

    Now I want to save this file as an add-in. I select “Save As…” from the file menu. I select “Excel Add-in…” from the file type and save it to the “…Roaming\Microsoft\AddIns\” directory as “MyMacro.xlam”. I go to options in Excel and follow the process of enabling the new “MyMacros” add-in in the AddIns dialog box, checking the box next to “MyMacro” and closing and restarting Excel. After restarting I go back to options and see “MyMacro” listed under “Active Application Add-ins”.

    However, I can not invoke the routine “MyMacro”. I can’t see it under macros on the View tab. I can not assign a hot-key to it. What am I doing wrong? I am missing a step that I can’t find explained anywhere.

    Thanks for your help!

  42. Is the installed add-in fine from the VBE?

  43. I seem to have exactly the problem that Shayne described. A small test macro, saved as an add-in, shows up in the add-ins check list window but, so far as I can tell, nowhere else. I can run it from the VBE.
    Regards,
    Craig

  44. Craig -

    Shayne’s using Excel 2007, what about you?

    Do either of you have any interface elements? Have you created a button that loads onto a menu, toolbar, or ribbon tab?

  45. After you install the add-in, and now you want it to work…how do you get it to open. I installed XY Chart labeler 7.0, expecting it to help me work on a time line, and I have no idea where to make it work.

  46. Shelly -

    After you select a chart, go to the Tools menu. At or near the bottom you’ll see a new item, XY Chart Labels, which opens a submenu. On the submenu are commands to add or move labels.

    In Excel 2007 the XY Chart Labels item is on the Add-ins tab.

  47. Hi Jon. Have just started using Office 2007. I have saved an Excel document to Add-ins by error. Was wondering how to retrieve/view/copy this file?

  48. Lyn -

    Don’t panic. If you don’t still have the xls (xlsm/xlsx) version of the file, open the add-in (like you would open any other file). Go to VB Editor, find add-in in the Project Explorer window, click the + to expand it, and click on ThisWorkbook. In the Properties window, change IsAddIn property to false. Now workbook is visible in Excel window. Save as the appropriate file type.

  49. That was all a complete foreign language to me..but in and out of microsoft sites excetera to translate this information I have managed somehow to retrieve the file.. I am very grateful to you Jon
    Thanks Lyn

  50. Can I add Microsoft Streets & Trips 2010 as an Excel 2007 Add-in?
    If so please walk me through it.

  51. Donna – I don’t know about that. I thought S&T was a separate application.

  52. I have created a public function and saved the excel file as an add in, I can open a new excel file and type the function into the cell and it works. However, I noticed that when I start typing the function name in the cell the drop down menu shows up with other functions but my function does not show up in the list. Is there a way to make the function show up in the drop down list? This would be nice if you have several functions made and you can’t quite remember what the name is, but as you start typing it you can find it on the list and click on it. All of the preloaded functions work this way, is it possible for a user defined function to work this way? Thanks.

  53. patrick telhomme says:

    I cannot add the add in button ont the QAT bar.. i get an error message
    tittle : Microsoft Visal Basic
    inside is a x button and a ok button and help button
    and cannot close thi message box ! i have to use end task program to close it and close the 2007 excel file.

  54. Make sure you have all of the latest service packs installed (Excel 2007 is up to SP2). Also you might benefit from running the diagnostics: Click the Office button in the top left, click Excel Options, then Resources, and finally Diagnose.

  55. Hi there,

    How do I add an add-in without a digital signature?
    Each time I open excel a message comes up stating that the add-in has no signature.

  56. Devraj -

    Your security settings may be causing this. Click the big round Office button, then Excel Options. Click Trust Center, then Add-Ins. Is the top box checked? Uncheck it, and your add-ins will not require a cert to run.

  57. It has been so frustrating in installing add-ins in microsoft excel. This really helped me installing in excel 2007. How about excel 2003?

  58. Priscila – See Installing an Excel Add-In for 2003 and earlier.

  59. I thought somebody on here had had something disappear; we’re trying to change the font color and our font has just disappeared.

  60. Jon,

    I read one of your earlier posts about using Solver in a macro. Very helpful! I am trying to have a macro that runs multiple Solver runs in sequence. Solver is already selected as an add-in in Excel for me, since I recorded the macro.

    Unfortunately, I get this error message (Compile error: Sub or function not defined) and SolverReset is highlighted in the VBA debugger window. I saw in your earlier post that I have to add a reference to Solver in VBA, but I go to the Tools menu, and References is all grey. Have you come across that?

    Thanks,
    Brian

  61. References will be gray if you haven’t stopped the code. I suspect it’s also gray if the active project is protected, but that’s not the case if you see which line had the error.

  62. Jon,

    Well, it was that easy. I restarted Excel, didn’t run the macro, and the References option was no longer in gray. It’s embarrassing how long I stared at that last night…

    Thanks,
    Brian

  63. I am not able to run one addin after i have downloaded it from one crack site which is digdb excel addin. It is giving me error compile error cannot find project or library\
    i have checked reference in tools option no missing link there, also addin has been included in excel 2007 options. Pls tell me what i can do to work this addin

  64. Nikhil – You want help with an add-in from a warez site? Purchase an official copy, and use the author’s customer support.

  65. Mr Jon,

    Temperature degree C

    RH% 50 62 74 86
    60 1 1 1 1
    0 1.00248 1.000448 1.000797 1.111353
    20 1.00166 1.0003 1.000536 1.000909
    40 1.000084 1.000152 1.000271 1.000262

    Here my task is to find out the value at 70 degree C and 35% RH.
    can u help me how to solve this by using excel

  66. Ankarao -

    You need to interpolate twice. First, insert a column between 62 and 74 degrees and populate it with interpolated values for 70 degrees. Then insert a row between 20 and 40% and fill it with interpolated values for 35%.

    Look up “interpolate” in Google if necessary.

  67. Hi, When I sign into excel I get an error message reading “an error has occured in attempting to connect the Plant Applications Server. Please re-install the plant applications excel add-in.” This add-in is local for my business, but I have gone through the steps in order to re-install but I continue to get this error message. Any advice?

    Thanks,
    Daniel

  68. Daniel -

    Is the Plant App Server a valid Excel add-in?
    When you manually install the Plant App Server, does it work as expected, or does the error occur immediately?
    Is the Plant App Server on a network share or on your local drive?

  69. it is very much heplful & easy to understand.

  70. Jon

    I’m having a problem in trying to download the solver add-in for excel 2007. I go through the process described, choose the add-in desired(solver) and I keep getting the message: Microsoft Excel cannot access the file: ‘C:\ Program Files (x86)\Microsoft Office\Office 12\LIBRARY\SOLVER\SOLVER.XLAM’ There are several possible reasons:

    Then it lists 3 reasons the file can’t be found. Is there a way to access that add-in?

    Jacqueline

  71. Problem downloading or problem installing? You can’t download SOLVER, you have to install it from the Office CD, if it was not installed when Office was installed. I don’t know why it would not have been installed with Office.

    Is the solver.xlam file present in the \library\solver\ path?

  72. I work for a shipping company and, I have a strange situation I have a worksheet which was created in Excel 2003 (which is to be used as template for Metrics collection), the columns M to Y are hidden in this excel sheet.

    Currently I am using Excel 2007, and want to view the hidden columns in this excel which created in excel 2003, when I go the View tab I find the unhide option is hashed out and I am not able to view columns M to Y, just do not know what to do. Do I need a patch or is there any Add In available to enable this.

  73. I had a similar issue, got over it by copying the contents from the excel 2003 work sheet and pasting the contents in a new excel 2007 sheet, not a clean way to unhide columns but it worked.

  74. Hello, The question that I have is how do I get my custom macros to show up as an add on the Add-Ins tab? I have created a macro and save it as an xlam file; gone through the load it to the Add-Ins process but not seeing it in the Add-Ins Tab or any other tab?

    Count.xlam
    ‘——————————————————————
    ‘ Counts the number of cells in a range that are not empty
    ‘ Inserts a new sheet and writes out column names and total counts per column
    ‘——————————————————————

    Sub M10_ColumnTotals() ‘
    rows(“2:2″).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(“A2″).Select
    ActiveCell.FormulaR1C1 = “=COUNTA(R[1]C:R[1048574]C)”
    Selection.AutoFill Destination:=Range(“A2:AX2″), Type:=xlFillDefault
    Range(“A2:AX2″).Select
    rows(“1:2″).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    End Sub

  75. I have followed all the above mentioned steps to add solver add in..but it is still not visible in Data tab. How can I get the same?

  76. Hi Jon,

    How do I use your add-in to get different size error bars for each bar of a bar chart? I am trying to do this using standard deviation but am a bit stuck?

    Thanks

  77. Lisa -

    Check out my recent tutorial on Custom Error Bars. You need to put the individual error bar values into the worksheet, and use them for the error bar data. The article shows how to do this manually or using my utility.

  78. I am trying to add Excel Connector for Salesforce.com just cannot figure out how to do it. I have Googled for noted but cannot find it. I also do not have the Add-Ins tab either. I am using Excel 2007.

  79. Thanks, Jon, for posting this amazing tip on Excel Addins! For the last 6 months, I’ve spent hours each month trying to figure out how to use my macro toolbar buttons, and not have Excel open the file the macros were saved in. With the slow cpu I’m using at work, the fewer the files open, the better.

    It was so frustrating, every blogger was happy to write details about adding macro toolbar buttons, while completely ignoring the fact that the Excel file is going to open each time the button is used.

    I had no idea that the solution was to save the file as an Add-In. Now, thanks to your post here, I will be able to create a host of functional macro buttons that I can use for all my files. I will take this info home, to add it to my home pc! A thousand thanks!

    Shari Thompson

  80. I cannot seem to find the DDXL add-in for 2007- I go to “add-ins” and “excel add-ins” but I swear it’s not there. Where the “add-in” ribbon should be at the top is now the “Developer” tab, which I have needed but I would love the DDXL add in! Thanks!

  81. Vicky -

    I’m not familiar with that add-in.

    If it’s not in the lists in the Excel interface, you’ll have to go to Excel Options > Add-Ins > Go, then browse to the add-in file and select it.

  82. Jon,

    I installed the analysis toolkit add-in in Excel 2007 but the new functionality doesn’t show up. I checked the list and the toolkit is installed. Why can’t I see it? And, how can I get non-admin users to have it? If I look at my installed add-ins as a non-admin, it is not installed.

  83. Kim -

    You have to install add-ins from the non-admin account which will use them.

    In 2007, when the Data Analysis Toolpak is installed, the Data tab of the ribbon gets a new group called Analysis at the far right, and this gets a Data Analysis button. The Analysis group will already be present if you’ve also installed the Solver add-in.

  84. Actually it showed up under non-admin. I did the exact same steps on my laptop and the data analysis “window” showed up. I think I need to reinstall Excel. Something just isn’t right.

  85. Thailand -

    Advice: That has absolutely nothing to do with this blog. Search Google for “Android GUI Designers”.

  86. Update: I “repaired” Office and the Analysis “window” showed up.

  87. It’s great!But I found a phenomenon during my using!
    I compared the fitted results to those of “local regression” in S-plus.
    It’s different. Maybe the paremeter was set differently. But would you like to tell me how can I get the same results in S-plus?
    Thanks anyway!!! Great job!! I love you.

  88. I assume you’re talking about the LOESS utility, which is described in a different post.

    We need to know some things before we can compare my utility’s results with those of S-plus.

    1. Does S-plus use the same weighting factor for points, according to X distance from the output X value to the X values of the points used in the moving fit?
    W(i) = (1 – X(i)^3)^3

    2. Is S-plus using the same number of points or fraction of the dataset in its moving fit?

    3. Are you comparing the S-plus results with my utility’s results on the same graph?

  89. thank’s
    so useful

  90. I’m using Excel 2007 on Windows XP. I have several user-defined macros and functions in an add-in. I want to invoke a macro from the ribbon (select ‘Developer’ click on ‘Macros’ then pick macro from list and click ‘Run’. However none of the macros in the add-in appear on the macros list. They all work if called by other means.

  91. Dougie -

    You would have to add a custom button to the ribbon for each add-in feature you want to invoke. In 2007 it’s easy enough to add a button to the Quick Access Toolbar. Click the down triangle at the end of the QAT, select More Commands, choose Macros in the left dropdown, select the macro, and click Add. Select the newly added macro and click Modify to change the macro display name and button image (unfortunately it’s a very limited set, and I don’t know how to add more images from the user interface). In 2010 you can also add commands to the ribbon tabs.

  92. Many thanks.

  93. Hi Jon! Your blog is absolutely wonderful, thanks for the information up there but unfortunately I cannot get it to work. I am trying to use an addon called Autobackup and it basically backs up my file as I go and work, after doing the Add-in, nothing happens. It seems that its supposed to install or something but nothing happens.

    Can you help me with this????

    Thanks!

  94. Sorry, I don’t know anything about Autobackup.

  95. I copied excel add-in from the NET (ColorFunction) and added as module. I was able to call the function on any workbook opened or worksheet.
    However after some time, when i call the function it does not work any longer. i go through the same process again but seems not to work.
    i am using office 2007. Any suggestions?

  96. “add-in from the net” implies you downloaded and installed an add-in, but “added as module” implies you copied the code into your own workbook.

    If you copy the code into a workbook, it may not be available to all workbooks. If you use an add-in, it should be available, but you may have better luck if the code is prefixed with “Public” as in “Public Sub MySub()”.

  97. I created an add-in and a macro for a pop-up calendar. But, how do I paste this feature into a column on another worksheet, so that the calendar pops up automatically for someone using it when they click on those cells?

  98. What causes the calendar to pop up?

  99. Hello,

    I am using an Add-In with Excel 2007. The add-in works fine locally. When I deploy an application to a web server, the application opens up Excel on the client PC with some data and formatting done. The spreadsheet that opens for the user was stored in Oracle. The spreadsheet sitting on the client needs to be able to read the add-in on the web server, but obviously can’t b/c all of our users dont have rights to the web server. Any idea how to get around this so the spreadsheet can use the add-in?

    thanks

    Lee

  100. Lee -
    Is the workbook downloaded to the client?
    There are all kinds of safeguards in place, more with each version of Office and Windows, that prevent unauthorized code from running, and unauthorized often means code that doesn’t originate on the client.
    It may be more effective to install the add-in on the computers of users who need it. You can build code into the add-in to check whether there is an update, and if so, to download and install the update.

  101. Thanks Jon for the reply…. Yes the spreadsheet is downloaded to the client when they click on the link. For my situaion it really isnt possible to place the .xlam file on each machine as we have many users all across the state.

    The reason I wrote the Add-In to beging with was because before any macros could be run on the spreadsheet when the users open it, it required them to do two things… a) enable macros(no big deal) and b) SAVE the file on their local file system. That is the one that was the sticking point. The save was required because the macros would not run unless it was saved. It kept giving the error saying “XXXX file cannot be updated because it is currently in use by another user. Username: ‘My own name’. Please open a read only copy or click notify or cancel…..”.

    thanks for your help.

    Lee

  102. Hi Jon,
    Moving around in the ‘refers to’ box inside Name Manager, to make changes/edits seems very cumbersome, especially when the formula that the name refers to is too long to fit inside the editing space (which is very small). Is there a good way of doing this? Everyone says you ‘can’ readily change things with the editor, but I am finding it difficult.
    Regards,
    Don

  103. The name manager in 2007+ is much larger and easier to use than in 2003-.
    You could also check out the Name Manager add-in from Jan Karel Pieterse at jkp-ads.com

  104. Thanks very much for the informative explanation :)

  105. Perfect description .. thank u

  106. Hi Jon!

    Thanks to your add-in, I was able to plot polar plots with ease. I’m having some other issues. As I only have data for 0 to 180 degrees, is it possible to delete the other half of the axis so that the plot would look like a protractor?

    Please let me know as soon as possible.

    Thanks,
    Sandeep

  107. Sandeep -
    Which add-in do you mean? I don’t have one for polar plots.

  108. Excel 2007 Add-in fails to run. I created a number of routines from another computer under Excel 2010 and saved the xlam file successfully. I moved Add-in to a server share and multiple co-workers were able to install the Add-in and utilize the functions without a problem. I understand the process and it’s installation.

    My son wanted the same functions on his Mac at his office. I sent the xlam file to him. He installed and none of the functions were available, as if the install did not work at all. Alt-11 shows the Project Explorer contains the xlam and the code from the module1 is displayable. So he got it and it was installed and it is visible. But it does not execute.

    To debug this I installed exactly the same xlam on my home PC that also runs Excel 2007. No matter what I do, it does not work. No add-ins work! I created a simple Public Function (ie: “Hello World”), saved it as an xlam, restarted Excel and included the Add-in as required. It does not work. So, it is not my coding.

    I turned to Excel Options – Trust center – Trust Center Settings.
    Trusted Publishers: blank
    Trusted Locations: List includes specific location of xla’s and xlam’s
    Add-ins: All boxes are unchecked
    ActiveX Settings: Enable all controls
    Macro Settings: Enable all macros and Trust access to the VBA project object model
    Message Bar: Show when content is blocked (nothing ever shows)
    External Content: Prompt; Prompt
    Privacy Options: All boxes checked.

    I have completely run out of options to choose. Any advice at all? … First one to fix this will get a $5 Starbucks card!
    Don

  109. I don’t think all that stuff with trust settings affects this.

    1. Are these subs that do things, or functions that return values?
    2. Are the procedures called through cell formulas, ribbon buttons, old fashioned menu buttons, the Macros dialog (Alt+F8), or through shortcut key combinations?
    3. Can you run the code manually via the VBA Editor, using F5 or F8?
    4. Did you install through the Add-Ins dialog?
    5. Do the procedures also not work if you load the add-in like a regular workbook?

  110. Don Emerson says:

    All good questions and thank you for your response.
    Public Function TCtest() As String
    TCtest = “What?”
    End Function
    Not too tough: Called from cell like this: =TCtest()
    If function code resides in add-in, it does not work.
    Project Explorer shows two VBAProjects. Addin contains code module. Workbook contains no code module.
    If I copy the code up to the workbook in Project explorer, it works fine.
    Does my description make sense?

  111. Jon,
    New information.
    When the add-in module containing the correct custom function, it does not appear as a valid choice as you type.
    Public Function TCtest() As String
    TCtest = “What?”
    End Function
    However, when you fully type in the function “=TCTEST()” in the cell (which you must do from memory because Excel is not prompting), you actually get a proper response in the cell: “What?”
    So, I assume that the real problem now, is not that the function is not executable.
    It appears that Excel will not prompt you step by step through each parameter as you type, like it will for other functions.
    Does THIS new information help you understand my issue?
    I’m now researching to see if there are limitations to the auto-prompting.

  112. Don -
    Glad you’ve updated your issue. I’d just tested with a dummy add-in, and the UDF worked as expected.
    To incorporate the UDF into Excel’s formula helper system, here’s an easy approach: User-Defined Function Argument Descriptions In Excel 2010. If you are using 2007 or earlier, this will not provide descriptions of the arguments, so here is a more detailed approach: Registering A User Defined Function With Excel.

  113. Thank you. I easily solve my problem.
    Cheers.

  114. I am using an add in called xCELLcolor. This add in allows you to count or sum by font color or fill color. I have to email the workbook to a remote office, but they do not have this add in on their machines, and so I know my functions will not work. They do not have to make changes to my workbook, they just simply have to view it. Is there a way to email it to another user and still be able to see the calculations that use the add in without them actually having to install it?

  115. Kendra -
    The add-in contains the functions used by your formulas.
    Copy the data, keep the same selecting, and use paste Special > Values. This overwrites the formulas with the values, so they see the calculations that were made on your computer.

  116. Dear Jon,
    thanks for your efforts and your ideas. But is there a way to change_all_ error bars at one time regarding percentage values? And what about formatting?
    And when I start thinking of it, several dozens of similar questions come to my mind.
    Earlier versions of Excel would allow to do ‘global’ changes much more easily. But now you only can change them one by one – which in my eyes is another setback Microsoft compels its customers to accept as a ‘feature’ what actually is a deliberate bug.
    For scientists like me, the 2007 version of excel is even more useless than the previous versions. The intricacy of the very procedure when trying to set up a simple xy-chart demonstrates it.
    Though it might give developers an additional income. But I’m not sure this should be the intention of such an expensive program.

    Best regards,
    Thomas

  117. As always, you have to change the error bars of one series at a time. Also, as always, you have to format the error bars of one series at a time. It has never been possible to change more than one series of error bars globally.

  118. Thank you again for this amazing add in. I just installed Office 2013 and it works like a charm. Only difference is that on a 64-bit system the path is:

    C:\Program Files\Microsoft Office 15\root\office15\xlstart

    Thank you again. I use Save DBF every day..

  119. Chris -
    That’s someone else’s add-in. All I provided in this article is the protocol for installation.

  120. Jon: I do apologize. I actually knew that and forgot. It was Gyula Gulyas at: http://thexlwiz.blogspot.com/

    You can delete these comments if you like.

  121. Hi Jon,
    Thanks for the information. The Analysis-Toolpak that I added in is in french and there does not seem to be an english option for me to install. I checked my language settings and they are all in english… Would you know whether/how I can switch the Analysis-Toolpak so that it is in english, or whether there is an english one available to download? I’m using Microsoft 2007.
    Thanks so much,
    Stephanie

  122. Was your version of Office French? I know there are language packs for Office, but I don’t know how extensively they treat supplemental materials, such as add-ins.

  123. Ok – thank you. Yes, I believe the original Office version was in french.

Subscribe without commenting

Trackbacks

  1. [...] Misschien kun je hier iets mee. Installing an Add-In in Excel 2007 | Peltier Tech Blog | Excel Charts __________________ Groet [...]

  2. [...] a look here to install an Addin for 2007 or higher. Installing an Add-In in Excel 2007 | Peltier Tech Blog | Excel Charts For 2003 or older Installing an Excel Add-In | Peltier Tech Blog | Excel [...]

  3. […] If you’re not sure how to install this addin into Excel, unzip the file and then follow the instructions here. […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites