HOW TO: How do I save and restore the location of a toolbar?

HOW TO: How do I save and restore the location of a toolbar?

Question

I have used the XLL+ AppWizard to create an add-in with a toolbar. If the user moves the toolbar, how do I make it reappear where it was left?

Answer

1. During the handling of OnXllOpenEx(), attempt to open the toolbar file, using the method CXlMacros::Open. If you fail to open it, construct the toolbar as usual.
2. During the handling of OnXllClose(), use the method CXlToolbar::SaveToolbar to save the toolbar to an Excel toolbar file.

Opening the toolbar file

Excel toolbar files must have an extension of xlb, e.g. myaddin.xlb. A simple location for the file is in the same folder as the XLL, with the same file name but a different extension.

1. Add a string variable to your application class, to hold the name of the toolbar file.

   class CTbsaveApp : public CXllApp
   {
   public:
       CTbsaveApp();
   
   // Names
   public:
       static LPCSTR m_pszDefName;
   
   // Menu
       CXlMenu m_menu;
   
   // Toolbar
       static LPCSTR m_pszToolbarName;
       CString m_strTbFile;
   
   ...
   };
   

2. In OnXllOpenEx(), calculate the toolbar file name and store it as m_strTbFile.

   BOOL CTbsaveApp::OnXllOpenEx()
   {
       // Set up menu
       ...
   
       // Calculate the full path of the toolbar data file.
       // This is the same as the add-in, with the extension "tlb".
       m_strTbFile = CXllApp::GetXllName();
       m_strTbFile = m_strTbFile.Left(m_strTbFile.GetLength() - 3) + "xlb";
   
       // Create toolbar
       // TODO: Change the toolbar name and captions
       //       Write an add-in function to be called when the tool button is clicked
       //       Add other buttons
       ...
   
       // TODO: Allocate any application-level resources
       return TRUE;
   }
   

3. Then call CXlMacros::Open to attempt to open the file. If it fails to load, construct the toolbar in the normal way.

   Note that you need to turn Excel error handing off for the Open command, so that you don't get an error message if the file is not found (which will always happen the first time the add-in is opened).

   BOOL CTbsaveApp::OnXllOpenEx()
   {
       // Set up menu
       ...
   
       // Calculate the full path of the toolbar data file.
       // This is the same as the add-in, with the extension "tlb".
       m_strTbFile = CXllApp::GetXllName();
       m_strTbFile = m_strTbFile.Left(m_strTbFile.GetLength() - 3) + "xlb";
   
       // Create toolbar
       // TODO: Change the toolbar name and captions
       //       Write an add-in function to be called when the tool button is clicked
       //       Add other buttons
       CXllApp::SetErrorHandling(FALSE);
       BOOL openedTlb = (CXlMacros::Open(m_strTbFile) == 0);
       CXllApp::SetErrorHandling(TRUE);
       if (!openedTlb)
       {
           CXlToolbarState tstate;
   
           CXlToolbar::AddToolbar(m_pszToolbarName, tstate);
           CXlToolbar::AddTool(m_pszToolbarName, 1, "SaveMe", "My addin function");
           CXlToolbar::SetToolBitmap(m_pszToolbarName, 1, IDB_BITMAP1);
           CXlToolbar::ShowToolbar(m_pszToolbarName, tstate, true, CXlToolbar::DockRight);
       }
   
       // TODO: Allocate any application-level resources
       return TRUE;
   }
   

Saving the toolbar file

4. During OnXllClose(), before you delete the toolbar, save its state to the toolbar file.

   void CTbsaveApp::OnXllClose()
   {
       // Delete menu
       m_menu.Destroy();
   
       // Save, then delete toolbar
       CXllApp::SetErrorHandling(FALSE);
       BOOL saved = CXlToolbar::SaveToolbar(m_pszToolbarName, m_strTbFile);
       CXllApp::SetErrorHandling(TRUE);
       CXlToolbar::DeleteToolbar(m_pszToolbarName);
   
       // TODO: Clean up any application-level resources
   }