Change Drive & Toolbars (2003)

Topic

New Reply

I have a file from which I produce a slimmer version for wider distribution. I have the process doing everything I want with 2 exceptions, I would appreciate any help/advice that anyone can offer on the following:

1. I have built a custom toolbar that is intended for use in the main file alone by anyone who uses it, to accomplish this aim I have attached the toolbar to the file and delete the toolbar form the application whenever the file is closed. However, Application.CommandBars(x).Delete does not unattach the file from the child workbook. I have also tried ThisWorkbook.CommandBars(x).Delete but all this does is generate a run-tim error 91 that I haven’t been able to resolve by following the help files.
2. When I save the child file I want to ensure it is saved to the same location as the main file. However, I need to be able to accommodate both network paths and mapped drives.

Any help will be gratefully received,

Reply | Quote

Replies

1. The Delete method of a command bar only deletes it from the list of available toolbars, it does not “detach” it. As far as I know, you can’t attach or detach a command bar using code. How do you create the child workbook? By saving a copy of the master? It might be better to create a new workbook and copy the necessary information into it.
2. You can use ActiveWorkbook.Path to get the path (don’t forget to add a trailing backslash if you concatenate it with the new file name)

Reply | Quote

Hans,

Thanks for your comments.

1. I thought I may have been barking up a non-existent tree as I had already spent a lot of time scouring the object browser. I save a copy of the master and remove the unnecessary worksheets, forms and modules during the process, due to the complexity of the file I think this is the only reasonable approach. I think the answer to my problem lies in removing only redundant actions from my event macros so that the child file can still manipulate the toolbar.
2. I never really thought about taking a simple approach, it works fine.

Thanks again.

Reply | Quote