COMMAND BAR - problems hiding-showing custom CBs

[itr674]

I had some custom command bars built that automaticaly start when files open.

I built a file and attached all customized CBs to it for a back up--problem is how do you show them in the backup file? I did this because I lost one the other day!!!!

They show up when I click "attach" and are listed in the "tool bar in workbook" section, but are not listed under the "toolbars" in the customize menu?
This message was edited by em on 2002-10-15 09:13
This message was edited by em on 2002-10-18 13:50
This message was edited by em on 2003-02-09 17:21

 

[tusharm]

What version of XL are you using?

The method I find safest across most XL versions is to have the workbook (or add-in) create and show the commandbar when it is opened (using the Workbook_Open procedure) and hide and destroy the commandbars when it is closed (using the Workbook_BeforeClose procedure).

 

[itr674]

tusharm

1. using Excel 2000.

2. These are pre-built command/tool bars that are attached to the file. We are using below listed code in ThisWorkbook to close users open toolbars that are open when the file opens and then delete the custom toolbar when the file closes.

<pre>Dim bClosing As Boolean

Private Sub Workbook_Activate()
Run "HideMenus"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
bClosing = Not Cancel
End Sub

Private Sub Workbook_Deactivate()
If bClosing = True Then Application.CommandBars("DTTM").Delete
Run "ShowAllMenus"
End Sub

</pre>

 

[tusharm]

<style>.eg{font-size:10pt;color:blue;font-family:Arial}</style>
<span class="eg"> </span>Bunch of comments.

Assuming that ShowAllMenus and HideMenus are procedures in your workbook, you don't need to 'run' them. Just use<span class="eg">ShowAllMenus</span>
In Workbook_BeforeClose, you have <span class="eg">bClosing = Not Cancel</span>. Cancel is meant for you to tell XL what to do; It is *always* false when XL calls the _BeforeClose procedure.

In the _Deactivate procedure you have <span class="eg">If bClosing = True Then Application.CommandBars("DTTM").Delete</span> Do you have a corresponding routine to build the DTTM commandbar? Or are you relying on DTTM being attached to the workbook?

Also, I don't think it is very smart to rely on the sequence in which events happen. AFAIK, there is no guarantee that _BeforeClose will always happen before _Deactivate. If I were in your shoes, I'd use BeforeClose to delete the DTTM commandbar. Then, in _Deactivate, add a safety check to ensure that you hide DTTM (as I presume you are doing) only if it exists.

A more general note on commandbars. MS made some changes in going from 97 to 2000 (or was it 95 to 97?) that replaced one set of toolbars and menus with a single set of commandbars. In doing so, it also dumped an older -- and IMO, easy -- way of attaching toolbars to workbooks. Now, I find it *safest* to create any menu/toolbar (i.e., commandbar) I want from *scratch* in the Workbook_Open procedure.