Unanswered: customise toolbars and macros disappear
when students create macros and new toolbars using Excel they save the file to there network drive. However, when they log back in they open the saved file and the macros have gone. I imagine this is because we delete profiles when they log out. is there anyway of saving the macro info in the Excel file when they save to there network space?
we clear the local profile because we have 2700 students here and the local HDD soon clogg up.
Im Surprised that macros disapear as they are stored in specific Workbooks whereas custom options are saved within the profile i think this its a *.tlb, This is a memory i had when clearing out all cuustpm options when excel kept crashing on me.
The macros in the personel macro folder is probably stored in the user profile so saving htis externaly may prevent problems i.e. storing these macros in a seperate workbook on the network drive
Excel saves the toolbar and menu settings in a file "Excel.xlb" until the application is closed once after menu/toolbar changes have been made the .xlb file will not be updated. Depending on the version of Excel and OS the file will be saved in 'Program File/Microsoft Office/Office/Excel.xlb' or the Application data path under users personal profile 'Application Data\Microsoft\Excel\Excel.xlb' in XP. With Office 2003 the file will be named 'Excel11.xlb' So if you've made menu changes and Excel happens to crash before you've closed it once the changes will not be saved.
Thats all good and more than you asked for. Like David mentioned Toolbars can be attached to the workbook, and if you attach the toolbar to a workbook it will reappear the next time the workbook is opened even if the menus are not saved to the application in the .xlb file. But toolbars are not attached by default. You have to attach them to the file using the Custom Menu options. To do this have your code workbook and make sure it is the active book.
First make a new custom toolbar for your book, add your buttons and attach to the corisponding procedures within the book, via the menu 'Tools->Macro->Macros...' (which you should have already done)
With the book active, Right-click in the toolbar area (a blank area not on a button) or select 'Tools->Customize' from the menu.
On the Customize dialog window select the 'Toolbars' Tab.
Click the 'Attach...' Button this opens an 'Attach Toolbars' window showing a list of custom toolbars and any thta may already be attached to the workbook.
Select the toolbar from the left list and click copy to move it into the right list.
Now with any desired toolbars copied to the right list close the Attach window and custome menu dialog and SAVE the workbook.
If you previously attached the toolbar you'll need to repeat this process to save any updates you made. I always delete the toolbars that are attached and re-attach them to make sure I get the updated version attached.
A cool thing to do if you have a workbook with only code that performs actions on other workbooks is to Hide the workbook and then save it, with an attached toolbar. Now when you open the workbook it will be hidden but the toolbar will be displayed providing the functionality you've programmed without showing a workbook. If it is only code to perform actions then it can be made and opened read only.
It doesn't matter if you've saved the book to your personal Macro folder location or if you open it from some other location the saved toolbars will still be attached and display.