custom menus - name? access? via properties 2

[cresbydotcom]

I was thinking to add a symbol in a custom menu to show which menu item result (toolbars in this case) were active or inactive.

The problem is I can't find an object or property that gives me the menu I have created. I have listed all CommandBars by name (120 including mine) but none come-up with the name of my menu. Only the enigmatic "Builtin Menus". Object browser does not reveal anything likely with the word "menu" except the CommandBar references.

 

[firefytr]

Hi there,

A couple of questions. What application are you using? And you want a list of all the active and inactive toolbars?? Where would you want this data? A message box?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cresbydotcom]

A good question - Excel (Office 2003 / XP)

No I have a list of all toolbars in a worksheet - it is easy enough to generate that because "CommandBars.Item" is indexed.
What I want to do is: to alter the custom menu that I call "toolbars". The alteration would be to show a symbol to show if a toolbar is active. And no symbol if that toolbar is inactive.

To do that I need an Object.Property specifically for "Menus" which I have failed to see in the Object Browser or by "watching" for likely properties.

I want the data in the running program and an analogue of it on my custom menu.

 

[CautionMP]

cresbydotcom,
Something like this?

[navy]Sub[/navy] DocumentCBs()
[navy]On Error Resume Next[/navy]
[navy]Dim[/navy] cbNew [navy]As[/navy] CommandBar, cbOld [navy]As[/navy] CommandBar
[navy]Dim[/navy] cbcNew [navy]As[/navy] CommandBarControl, cbcPopup [navy]As[/navy] CommandBarControl
Application.CommandBars("Toolbars").Delete
[navy]Set[/navy] cbNew = Application.CommandBars.Add("Toolbars")
[navy]Set[/navy] cbcPopup = cbNew.Controls.Add(msoControlPopup)
cbcPopup.Caption = "All toolbars"
cbcPopup.Style = msoButtonCaption
[navy]For Each[/navy] cbOld [navy]In[/navy] Application.CommandBars
  [navy]Set[/navy] cbcNew = cbcPopup.Controls.Add(msoControlButton)
  [navy]With[/navy] cbcNew
    .Caption = cbOld.Name
    [navy]If[/navy] cbOld.Visible [navy]Then[/navy]
      .State = msoButtonDown
    [navy]Else[/navy]
      .State = msoButtonUp
    [navy]End If[/navy]
    .Style = msoButtonCaption
  [navy]End With[/navy]
[navy]Next[/navy] cbOld
cbNew.Visible = [navy]True[/navy]
[navy]Set[/navy] cbOld = [navy]Nothing[/navy]
[navy]Set[/navy] cbNew = [navy]Nothing[/navy]
[navy]Set[/navy] cbcNew = [navy]Nothing[/navy]
[navy]End Sub[/navy]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[cresbydotcom]

That code actually produces a toolbar not a menu. Which indicates menus are not accessable as objects with properties. It may be that is the only way to produce this. I wanted to remove clutter by parking the list in a menu. I will see how this goes. The actuall list is a subset - only my own personal toolbars with myriad buttons for simple things like dumping the date in UK format into a cell (VBA seems reluctant to acknowledge the UK in many respects)
or to date and up-issue a drawing by saving as

foobar_issB.xls (eg) instead of _issA -

if the macro makes it easy it makes other things tidy.
I don't do tidy - I do macros..............

 

[cresbydotcom]

Many thanks - I have persevered and the clue is that in this version of Excel the menu bar (Worksheet Menu Bar) IS a toolbar detachable just like the other toolbars. Never noticed it in earlier versions.

as a result I tried this and it gives me a whole swag of properties. I expect there is one to specify where it is attached - assuming I have to move it.

eg my custom menu is item(11)

Application.CommandBars("Worksheet Menu Bar").controls.Item(11).controls.Item

 

[firefytr]

Ah, ok. Yes, the Application.Commandbars("Worksheet Menu Bar").Controls will give you the control on that menu. Your question was a little hard to understand. One quick question. What exactly do you mean by "active" and "inactive"? Do you mean "enabled" and "disabled"? What constitutes active/inactive exactly?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[CautionMP]

cresbydotcom,
I concur with firefytr, I'm still a little lost, but I'm glad what I posted helped.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[cresbydotcom]

I guess if I noticed the detachable menu bar earlier (well I did but failed to connect in the context of this problem).
Given that assumption it makes my question a little more understandable - if redundant.

Actually - only by detaching it can you see what the caption/name is. And only by looking closely for the little marker on the extreme left, do you realise it is detachable.

What I meant was "visible" rather than "enabled" - as specified in properties.

This is a new PC on XP and OfficeXP to suit. Home and work machines have been Win98 and Office 97 (1897). Our problem is we still have legacy (DOS!!!!) applications and designs that were drafted/programmed with them - XP is a culture shock.

 

[firefytr]

Okay, maybe that is where we are getting lost in translation. It seems you are talking about toolbars and not menubars. Do you mean those such as Standard, Formatting, Visual Basic, Drawing, etc? And you want a custom menu that lists all of them and checking which are "enabled" (visible) and unchecking those which are "disabled" (invisible)?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cresbydotcom]

Yea and given the kind of property I can look for something to copy or inspect (eg toolbars). Or just play with properties. Once I have a likely property I put it in the watch window and expand it (eg after a single-step).

Mind you I lost the new menu because I dragged and dropped into a worksheet when in "customise" mode.

 

[newfrontiers]

Hello. I'm trying to create a toolbar that will open some forms and reports. I have been trying to create this for a while, but I do not understand how to attach code or a macro to a button in the toolbar. I see the On Action drop down but nothing appears to select.

Could someone help.

Thanks.

 

[cresbydotcom]

In the app (sounds like Access) right click on the area of toolbars or a toolbar and choose Customise.

choose the "Toolbars" tab, and create new toolbatr (give it a name)

choose the "Commands" tab, and select Macros.

in the right pane drag and drop the "Custom Button" Icon to your new Toolbar

right click on that Toolbar on the actual button and change a few things. The macro "assign" is at the bottom.

Good luck

 

[newfrontiers]

Thank you for the information. It saved me a lor of time. Here is a star for you.

 

[firefytr]

Hi cresbydotcom, sorry I haven't been back in a while, got really busy and am just now playing catchup here. Sorry for any inconvenience.

If you are still working on this issue, I don't think there is a real efficient way to programmatically control/view properties of toolbars. Even showing which toolbars were visible/enabled would seem a waste of time (IMO), so I'm still a little lost.. :-(

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cresbydotcom]

It is more to do with finding the things in a hurry. And in case someone decides to remove them on MY MACHINE! Job done now. Or I can hide them quickly.

Now if you have any ideas on how to set the default user in Outlook (other than manually) I have a thread on the subject but no answers yet.

http://www.tek-tips.com/viewthread.cfm?qid=1284852&page=1

Thankx in retro and advance