Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    customising commandbars (any/2k)

    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.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    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.
    Gre

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: customising commandbars (any/2k)

    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?)
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    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?

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    <hr>what exactly is the problem<hr>
    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.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: customising commandbars (any/2k)

    Oh OK. You just threw me as I haven't seen too many posts about code that works perfectly! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> FWIW It works for me too. Am I right in assuming that it doesn't work for someone else?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    *.JPGs at dawn <img src=/S/smile.gif border=0 alt=smile width=15 height=15>? 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.
    Gre

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    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.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    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?
    Gre

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising commandbars (any/2k)

    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?

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising shortcut menus (any/97/2k)

    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 <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> 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 <img src=/S/threadhead.gif border=0 alt=threadhead width=28 height=31>.

    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.
    Gre

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising shortcut menus (any/97/2k)

    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 <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=95982&page=&view= &sb=&o=&vc=1>brucef2112's post</A>, 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.

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising shortcut menus (any/97/2k)

    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 <img src=/S/doh.gif border=0 alt=doh width=15 height=15>. 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 <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=36107&page=1&view =collapsed&sb=2&o=0&fpart=>Clear the Clipboard</A> 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? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>).

    Input to date much appreciated.
    Gre

  14. #14
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising shortcut menus (any/97/2k)

    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?

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: customising shortcut menus (any/97/2k)

    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?
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •