customising commandbars (any/2k)

Topic

New Reply

Both of these work for me in 2k.

Sub bcrtModifyMenu33a()
Application.CommandBars(33).Controls.Add Type:=msoControlButton, Id:=443, Before:=1
End Sub

Sub bcrtModifyMenu33b()
Application.CommandBars(“Nondefault Drag and Drop”).Controls.Add Type:=msoControlButton, Id:=443, Before:=1
End Sub

Has anyone else had problems trying to modify this commandbar?

Also, whilst perusing the list of commandbars available, I came across one that doesn’t make immediate sense.

“Phonetic Information” – 54 in 2k – anyone know where that appears and why it’s called that? FYI, all it has is cut copy & paste.

Reply | Quote

Replies

Not quite sure what you mean by “this command bar”. “33” is the Document Bar: provided you don’t have a maximised workbook, it’s a shortcut menu accessed through the window frame at the top. The Nondefault Drag and Drop Bar is quite different.

At a guess, “Phonetic Information” is linked to some of the Foreign Language modules. It may have underlying code that is prompted by Language/Location settings; revealing other icons at that stage. If Cut, Copy & Paste turn out to be 1, 2 & 3, then there may be no obvious way to test. Try working with Add set to other than Before:=1. If they turn out to be some other numbers, then the inference I made could be correct.

Reply | Quote

Ok, when I run the code below – in 2k:

Sub ComBarList()
For Each ComBar In CommandBars
Debug.Print ComBar.Name, ComBar.NameLocal, ComBar.Visible, ComBar.Index
Next
End Sub

then in the immediate window I get Non Default Drag and Drop next to ID 33, and when I run the two lumps in my earlier post, it is the non-default drag and drop that is modified. What do you get in the immediate window? The Document Bar for me is 31. Are you looking at a 97 list of ID/names? Which version are you working in?

Reply | Quote

*.JPGs at dawn ? Hopefully it will upload! If not, I will retry when I get home!

The software says it’s O2K-SR1. It is an English language version. When I run the first of your snippets, it changes the Document Bar shortcut menu (at the top of the Workbook window. This is only accessible when the Workbook is not maximised.

The method I use is to access the CommandBars by Name (e.g. Document Bar instead of 33 or 31) and the controls by Number (e.g. 1 instead of msoControlButton). Names seem to give a perormance hit, but there now seem to be other issues. As they say: Your Mileage May Vary.

Reply | Quote

all the above testing was done on 9.0.4402 SR-1

I’ve just looked on 97 and the difference appears to be that between 97 and 2k refresh was inserted at 18 and then PivotChart Menu was inserted at 21, shuffling everything with a higher ID than these two bars up by two. leastways that’s how it looks to me. It would be interesting to see what other people can see in case it’s my system? and what those using XP can see – for a laugh!

The key here is of course as you say to use names – but if you want to alter the cell, column and row toolbars for page layout view you can’t do this as they are duplicated – see attached – and I’ve always found that if you do use the name it changes the normal view menu’s – i.e. the lower numbered ones.

If you’re having problems attaching, remember to attach/reattach after your last preview otherwise it’ll get lost.

Reply | Quote

Well, I have since tried uploading as a *.jpg, a *.gif and a*.bmp! None showed up in the preview; no matter how many times I re-uploaded! To date, I have uploaded *.xls, *.txt and *.zip without any problem. If no attachment shows up, this is the after the fact warning .

I have however identified the difference. I have 4 Custom Menus: 2 built into personal.xls and 2 from Add-Ins. The 2 homemade ones tuck in after 19. The first Add-in (X-Bar) tucks in after 91 (89 on a “clean” layout) and the second (Excel Spy) after 96 (93 on a “clean” layout). The moral of the story being that having homemade customised menus throws off third party coded menus that try accessing by number and not by name. As for the logic of the numbering, go figger!

On the issue of accessing the Print Layout Menu, I tried trapping for the triggering of Print Layout view, but this did not make any difference.

The bulk of these customisations to shortcut menus save only one click per use, but they also mean that your eyes (and personal focus) don’t need to wander around the screen. For me, they’ve been a significant time (and hassle) saver.

Back when I was originally building the code (in 97), I tried see whether I could grey out the Paste Special commands that I had imported onto the Cell Menu – to show the Commands as unavailable. I’ve since done a brief search on the Lounge to see whether there was any code testing for the Clipboard being active. All I’ve found so far is the well-worn issue of hiding the 2k Office Clipboard Menu. Any ideas?

Reply | Quote

Right. All my custom menus – either add-in or not – all tuck in at 89 or above. So that’s why we’re talking different numbers. At least that’s sorted!

I’m still not sure what you mean by Print Layout Menu: if you mean the cell, column and row menus for page break preview then in a totally automated system you’d have to access it by something along the lines of:

low_row_id = application.commandbars(“row”).id
high_row_id = low_row_id

do
high_row_id = high_row_id + 1
loop until application.commandbars(high_row_id).name = “row”

If you mean the menu that you get in print preview – which seems more likely – I don’t know how to call this and I’m not sure you can. None of the commandbar names seem to fit and in addition, if you are in print preview you can’t do anything in the VBE, which to me indicates that print preview is more of a dialog or message than a seperate view like page layout/normal views. I don’t know how to get inside the built in dialogs and message boxes.

I totally agree with you as regards the time saved – my favourites are freeze panes and paste special values on the cell, row and column shortcut menu’s – as a one off it’s only a couple of clicks but if you’re using those commands over and over.

If I understand you correctly, I would probably check for the enabled state of the paste button on the edit menu of the workbook bar. application.commandBars(1).controls(2).controls(5).enabled should get you that (as long as yours are the same as mine!) but I’m not sure how I’d go about updating the control to cope with a changing state. Somewhere recently in the lounge I have been reading code to do this via class modules. it’s either that or the worksheet selection change event. I’ll see whether I can find it again. But do you really need this – how often is the excel clipboard empty?

Reply | Quote

Unlike you (it seems), periodically it becomes simpler to work in Page Break Preview mode. What I was hoping to achieve was to enable the customisations to the Cell Menu (particularly) when working in that mode.

As I went about this quite some time ago, I used a rather simplified approach. Nonetheless, if you take the default Cell, Row and Column menus in xlNormal view in a completely fresh workbook, you’ll find that not all the Controls are visible. In the Cell menu, my recollection is that some of the hidden Controls are the additional Comment Controls. The others relate, I believed, to Page Break Preview mode. This appears to be the problem: accessing the menu switch. Maybe the problem is too obscure. If you run a customisation sub on the Cell Menu (by name) when Page Break Preview is active, the customisation only shows up once you switch out of Page Break Preview. What do you think?

For Paste Special, I actually insert an entire submenu (with 10 commands). The longer-winded godsends are having Sort and Subtotals right at the select point in the Columns Menu (saves the wrist trembling), one-click access to controlling the direction of the Cursor after a click, and also to Custom Styles. Also things like the Ply Menu can be used hiding and unhiding sheets. Having the customisations on shortcut menus takes out clutter from toolbars; definitely maximising screen real estate.

The enabled state is where I started. This runs fine until the state changes. Thus it needs to get re-triggered once the Clipboard gets filled/cleared. The practical problem is in the middle of a sidetrack one can forget that the Clipboard’s become empty due to something else entirely. Thus greying out the customised Paste Special submenu is needed. I’ll see if something yields up on Worksheet Change or Class Modules .

The other timesaver is being able to float the shortcut menus nearby when you’re doing the same non-automatable action repetitively: a piece of free code called Pop2Bar.

Reply | Quote

I have to admit that the only times I go into page break preview are when I’m trying to help other people out! But I don’t think you’re going to get away from using the index numbers as – for me – using the names “cell”, “column” and “row” always applies changes to the normal view commandbars of those names and not the page break preview commandbars – which, although they have the same name, are not the same. As I said, the only way I can think of that you’d be able to do this with any certainty is to get the default ID that an installation of Excel returns when you call by name and then search for the ID of any other commandbar with the same name.

Surprisingly, I managed to find brucef2112’s post, to which I referred above. Take a look and see if this is of use to you. I’d also be interested to hear comments from other people on whether selection change or class module is the way forward? Selection change will have a high overhead, but I don’t know what the overhead is if you go down the class module route.

Reply | Quote

Thanks for the reminder about brucef2112’s post with the Class Module. I’d glossed past it because some of the code was attached to an individual worksheet. So far I’ve tried setting it to toggle on whether one of the other Shortcut Menus has a disabled Paste button: “Set clsMenuControlModule.MenuControlClass = CommandBars(“Formula”).Controls(3)”. This is not an Object and so the code hangs on this statement . Any inspiration?

I’m not clear how Selection Change could help here as we would have to test for the Clipboard being full (i.e. not empty). The Clear the Clipboard thread seems to suggest that all the possibilities of testing for the Clipboard’s state – short of actually clearing it through the API – are not available.

On the Cell, Column and Row Menus during Print Preview mode, the code you suggested just delivers the same Index number as the default (non-Print Preview mode) Menus. The total number of Command Bars does not increment in Print Preview mode either. On a closer look, the “additional” Controls all appear on the bottom of the menu. The Controls that disappear (also all at the bottom) are – AFAIK – Controls that were new in either 97 or 2000. This may suggest that what is running here is code that has not been touched since 95. Thus these subtypes of Menus may not have been really brought into the CommandBar Object reorganisation that came in with 97. If so, they may be in a somewhat different part of the Object Model (submerged? ).

Input to date much appreciated.

Reply | Quote

Run the two following lumps of code and let me know what you get:

Sub unkamunka2()
low_row_id = Application.CommandBars(“cell”).Index
high_row_id = low_row_id
Do
high_row_id = high_row_id + 1
MsgBox high_row_id & Application.CommandBars(high_row_id).Name
Loop Until Application.CommandBars(high_row_id).Name = “Cell”
MsgBox “out of loop”
MsgBox high_row_id & Application.CommandBars(high_row_id).Name
End Sub

Sub unkamunka3()
low_row_id = Application.CommandBars(“cell”).Index
high_row_id = low_row_id
Do
high_row_id = high_row_id + 1
MsgBox high_row_id & Application.CommandBars(high_row_id).Name
Loop Until Application.CommandBars(high_row_id).Name = “cell”
MsgBox “out of loop”
MsgBox high_row_id & Application.CommandBars(high_row_id).Name
End Sub

interesting?

Reply | Quote

In other words, this second set of “submerged” Cell, Row and Column Menus has been ring-fenced as part of the core code. This part of the Object Model has not been “exposed”. (I take it that you’ve run an AddTo procedure and been greeted by the Invalid Procedure – RTE 5 message.) Presumably, something is going on with GDIxx.dll to change these Menus in Print Preview mode that later levels of Excel have left well alone. Wonder how they’ll tackle it when they try to get up to 64-bit?

Reply | Quote

OTOH, it so happens these “Protected” Shortcut Menus CAN be accessed by setting the CBar Type as 2, you end up with only the 50 or so Shortcut Menus. (If you want to customise all your Shortcut Menus the same way, this is quite tidy.) As I’m only really looking to customise the Cell Menu in Print Preview, the overhead may not be so great.

On a different area, I’ve been trying to get one-click access to the WIndows|Unhide command. What I’ve got so far for the Form Initialize event is extremely buggy. This is largely because in order to access the relevant Properties I’ve ended up with Object soup :

For i = 1 To Workbooks.Count
If Windows(i).Visible = False Then
ListBox1.AddItem (Workbooks(i).FullName)
End If
Next

Among other things, the Visible = False filter doesn’t work properly. Also the thing falls down completely if you have multiple windows open in a single workbook. MSKB Q248178 effectively seems to cite multiple workbook windows as a known issue when accessing workbooks from outside Excel.

Reply | Quote

Hi,
How about something like:

For i = 1 To Windows.Count
    If Windows(i).Visible = False Then
        ListBox1.AddItem (Windows(i).Caption)
    End If
Next

Seems to work ok in Excel 2000.
Re the shortcut menus, I’m not sure I understand the problem exactly. As far as I can tell the higher index of the two cell menus is the pagebreak preview one, so as long as you ensure you pick that one (using Brooke’s routine or loading the 2 indices into variables or an array and picking the greater one) you should be able to customise it as usual. What did I miss? (I know there’s something )

Reply | Quote

Try this and see what you get (in both views):

Sub Undo()
For Each cbar In CommandBars
If cbar.Type = 2 Then
cbar.Reset
If cbar.Name = “Cell” Then
cbar.Controls.Add(Type:=1, ID:=443, before:=1).Caption = “&Freeze Panes”
End If
End If
Next
End Sub

Reply | Quote

Re post 103840,

Apart from the fact that I don’t think the commandbars have been ring-fenced as such, it’s just that you have to use a workaround to get at them, I’m afraid the rest of your comments are way over my head. I’m very much the wrong person to be trying to engage in an excel design review!

Re post 103882,

You do not need to restrict to type 2. You’re still going to end up with a list of commandbars that includes two with the name Cell. To then make changes to both you are still going to have to access at least one via the index. So invoking type = 2 is a waste of time.

Re post 103918

I modified your code slightly but it does the same thing

Sub Undo()
For Each cbar In CommandBars
If cbar.Name = “Cell” Then
cbar.Reset
cbar.Controls.Add(Type:=1, ID:=184, before:=1).Caption = “&Record New Macro…”
End If
Next
End Sub

this does alter both menus. It is doing this by looping through the collection – which is done on the index, not the name – see my earlier comment about having to use the index. But the problem here is that you’re making the same changes to both menus. Which you might not want to do.

Sub Undo()
For Each cbar In CommandBars
If cbar.Name = “Cell” Then
MsgBox cbar.Controls.Count
End If
Next
End Sub

the above indicates clearly that the two cell menus are different. I would be very hesitant to make the same changes to both even if all the changes went in at before = 1.

Reply | Quote

Thank you. Pardon my ramblings on

Reply | Quote

Hi Rory,

Thanks very much. The code is very much like what I had originally started out with, but I had overlooked the Caption property.

On Page Break Preview: it just seems strange that these three CommandBars (only) require a specific workaround. Sort of has the makings of a

Reply | Quote

My suspicion would be a lack of imagination or foresight on the part of an MS programmer!
Haven’t looked at the other post in detail yet – will do when I get a minute and let you know if any light shines through the New Year hangover….

Reply | Quote

Hi,
What event are you planning to use to trigger this? I can’t think, off the top of my head, of any event that would be triggered by the enabling/disabling of that button. I suppose you could (assuming the API can provide the necessary functions, which I haven’t investigated) add something to your paste routines to handle the situation that there’s actually nothing to paste.

Reply | Quote

FWIW, the following, which is just what I normally use but with a global replace of 26 for 23, runs fine on my system – you can check the index against the screenshot I attached earlier! –

Sub bcrtModifyMenu26()
Application.CommandBars(26).Controls.Add Type:=msoControlButton, Id:=443, Before:=5
Application.CommandBars(26).Controls.Add Type:=msoControlButton, Id:=370, Before:=5
With Application.CommandBars(26).Controls.Add(msoControlButton, , , 5, True)
.Caption = “Paste Formula”
.OnAction = “bcrtPasteFormula”
End With
Application.CommandBars(26).Controls(5).BeginGroup = True
Application.CommandBars(26).Controls(7).BeginGroup = True
End Sub

so if you’re getting an error I suspect there’s something else going on that we haven’t considered yet. Can you access other menu bars by index or is it just those three?

Reply | Quote

More haste, less speed: I substituted the IndexNo for the Name WITHOUT removing the quotation marks!! I am still inclined to favour the “loop for name” method, however, because: (1) it appears logical that the view mode is unlikely to change underlying functionality needs; and (2), with the IndexNo being possibly user-specific, it appears more reliable.

Have yet to solve the API issue re the enabled Clipboard.

Reply | Quote

Hi Brooke,
As far as I know, the Phonetic info and guides only apply to Japanese language data. I think it has something to do with displaying Kanji characters phonetically as Hiragana characters (and possibly Katakana as well, though as best I recall those are only used for foreign words which probably have no real Kanji equivalent).
As far as your commandbar 33 thing goes, what exactly is the problem if it works for you in 2k? Does it not work in 97? (Did that commandbar exist in 97?)

Reply | Quote

[indent]

---

what exactly is the problem

---

[/indent]what would you say if I said I don’t know?

it works fine for me and I don’t have a problem that I’m aware of – please don’t inform me if you know different! It’s just that I started a PM thread with someone about something completely different and we just got carried away and had our own little mini-discussion going before you could say “Jack Rabbit”, so I decided it was time to come out into the open, so that others could help as well.

Reply | Quote

Oh OK. You just threw me as I haven’t seen too many posts about code that works perfectly! FWIW It works for me too. Am I right in assuming that it doesn’t work for someone else?

Reply | Quote