COMMAND BAR - problems hiding-showing custom CBs

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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).
 
Upvote 0

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
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>
 
Upvote 0

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
<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.
 
Upvote 0

Forum statistics

Threads
1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top