Solved: Tools Menu' ID:=XXXX ?

[Erdin? E. Ka]

Hi everyone.

I want to reload Tools Menu back.

I tried the codes below. But i couldn't do it.

[VBA]Sub This_Is_A_Space_Plum_MyVBA_If_You_Eat_It()
Application.CommandBars("Worksheet Menu Bar").Controls(6).Add
End Sub[/VBA]

Then i wanted to try the codes below. But this time i can't find the ID number of Tools Menu.

[VBA]Sub ReLoad_Tools_Menu()
'XXXX = ?
Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:=msoControlButton, ID:=XXXX, Before:=6
End Sub[/VBA]


Can anybody tell me how to i can solve it please?
Tools Menu' ID:=XXXX ?


Moreover, if it possible; can anybody give a list of the ID numbers of Controls? Its' better then only one control.


Thanks in advance.

[Bob Phillips]

30007

[vba]

Sub ListIds()
Dim ctl

For Each ctl In Application.CommandBars(1).Controls
Debug.Print ctl.Caption, ctl.ID
Next ctl

End Sub
[/vba]

[Erdin? E. Ka]

Hi xld thank you very much to kindly help and care.

[VBA]Sub ReLoad_Tools_Menu()
Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:=msoControlButton, ID:=30007, Before:=6
End Sub[/VBA]

I tried like this but it returned the error;

Run-time error '5':
Invalid procedure call or argument


Also,

[VBA]Sub ListIds()
Dim ctl

For Each ctl In Application.CommandBars(1).Controls
Debug.Print ctl.Caption, ctl.ID
Next ctl

End Sub[/VBA]

After i run this code did nothing.

Where is my mistakes? Can you help me?

Thank you.

[Erdin? E. Ka]

Hi everyone,

I found the solution!!!

[VBA]
Sub ReloadToolMenu_2()
Application.CommandBars("Worksheet Menu Bar").Reset
End Sub
[/VBA]

And i modified the other codes:

[VBA]
Sub ListIds()
Dim ctl
i = 1
For Each ctl In Application.CommandBars(1).Controls
Cells(i, 1) = ctl.Caption
Cells(i, 2) = ctl.ID
i = i + 1
Next ctl
End Sub
[/VBA]


But now i wish to write a VBA code that includes Split Function to populate all menu names and ID numbers as Folder/SubFolder arranging.

For Example:

Column A: Menu --- Column B: Sub Menu --- Column C: ID Number

File ____ 30002
____ New... xxxx?
____ Open... xxxx?
____ Close... xxxx?
... xxxx?
... xxxx?

Edit ____ 30003
____ Can't Undo xxxx?
____ Can't Repeat xxxx?
.... xxxx?
.... xxxx?
.... xxxx?
Help ____ 30010
____ About Microsoft Office Excel. xxxx?

Thanks in advance.

[Bob Phillips]

Try this

[vba]

Option Explicit

Private iLevel As Long
Private iRow As Long

Sub MainControls()
Dim oCB As CommandBar

Worksheets.Add.Name = "Controls List"
iRow = 0
For Each oCB In Application.CommandBars
iLevel = 1
SubControls oCB
Next oCB
End Sub

Sub SubControls(ParentCtl As Object)
Dim ctl As Object
iRow = iRow + 1
On Error Resume Next
Cells(iRow, iLevel).Value = ParentCtl.Caption
Cells(iRow, iLevel).Value = ParentCtl.Name
On Error GoTo 0
Cells(iRow, iLevel + 1).Value = ParentCtl.ID
If ParentCtl.Type = msoControlPopup Or _
ParentCtl.Type = msoBarTypeNormal Or _
ParentCtl.ID = 265 Then
For Each ctl In ParentCtl.Controls
iLevel = iLevel + 1
SubControls ctl
iLevel = iLevel - 1
Next ctl
Else
End If

End Sub
[/vba]

[Erdin? E. Ka]

It's Fast! and It's Excellent!
Now, let my dream come true, thank you very much XLD. Really great job.
I love here..

[Bob Phillips]

It's Fast! and It's Excellent!

And it's recursive!

I love here..

Me also!

[tdm100]

xld,
I thought your code was a nifty idea and decided to run it but I encountered an error.
Is it possible you can tell me why?


Cells(iRow, iLevel + 1).Value = ParentCtl.ID
Object doesn't support this property or method (Error 438)

[tdm100]

hmmm,,,,under further review

: : Expression : Value : Type : Context
Watch : : ParentCtl.ID : <Object doesn't support this property or method> : Variant/Integer : Module1.SubControls

Watch : : ParentCtl.Name : "Worksheet Menu Bar" : Variant/String : Module1.SubControl

Watch : : ParentCtl.Caption : <Object doesn't support this property or method> : Variant/Integer : Module1.SubControls

Only the Name property seems to be recognized

[Bob Phillips]

No idea off the top. Can you post the workbook?

[tdm100]

All i did was open a NEW Workbook in Excel 2000
Inserted a Module, pasted your code, then I immediately executed it

Using the watch window
: : Expression : Value : Type : Context
Watch : - : ParentCtl : : Object/CommandBar : Module1.SubControls

I've been rummaging through the ParentCtl tree and I cant find an ".Id."

Additionally, every time I attempt to write ".Id" in the VBE the IntelliSense changes it to UPPERCASE ".ID"

Excel 2000

[tdm100]

I just found an Id under ParentCtl.Controls.Item1.Id
Watch : - : ParentCtl : : Object/CommandBar : Module1.SubControls
- : Controls : : CommandBarControls/CommandBarControls : Module1.SubControls
- : Item 1 : : Variant/Object/CommandBarPopup : Module1.SubControls
: Id : 30002 : Long : Module1.SubControls

[Desert Piranha]

xld,
I thought your code was a nifty idea and decided to run it but I encountered an error.
Is it possible you can tell me why?


Cells(iRow, iLevel + 1).Value = ParentCtl.ID
Object doesn't support this property or method (Error 438)

Hi all,

I have the same exact thing going on.

xl2000 also, would that be the issue?

[Bob Phillips]

That seems to b e the problem, I just tried it on 2000 and I also get the problem. Odd, seems commandbars don't have an id in pre-XP. I will need to investiagte further, but in the meantime, try this version

[vba]

Option Explicit

Private iLevel As Long
Private iRow As Long

Sub MainControls()
Dim oCB As CommandBar

Worksheets.Add.Name = "Controls List"
iRow = 0
For Each oCB In Application.CommandBars
iLevel = 1
SubControls oCB
Next oCB
End Sub

Sub SubControls(ParentCtl As Object)
Dim ctl As Object
Dim ctlId As Long
iRow = iRow + 1
ctlId = 0
On Error Resume Next
Cells(iRow, iLevel).Value = ParentCtl.Caption
Cells(iRow, iLevel).Value = ParentCtl.Name
ctlId = ParentCtl.ID
On Error GoTo 0
Cells(iRow, iLevel + 1).Value = ctlId
If ParentCtl.Type = msoControlPopup Or _
ParentCtl.Type = msoBarTypeNormal Or _
ctlId = 265 Then
For Each ctl In ParentCtl.Controls
iLevel = iLevel + 1
SubControls ctl
iLevel = iLevel - 1
Next ctl
Else
End If

End Sub
[/vba]

[tdm100]

Hi all,

I have the same exact thing going on.

xl2000 also, would that be the issue?

Here's waht I came up with having to use that old and outdated Excel2000
HA!

[vba]
Sub tdm100Mess()
Dim TLbar As CommandBar
Dim cTrl As CommandBarControl
Dim ws As Worksheet
'/\ most of the ws and related to worksheet BIZ is personal preference....
'/\ when I was creating and hence debugging
'/\if it's a 1 shot deal simply use Worksheets.Add.Name = "Controls List" instead
Dim ShtName As String, StrtRW As Long, TLbrName As String, TBVisble As String
Dim TLbrCtrlCnt As Long, TLbrTypes As Long, msoType As String
Dim cTrlTypes As Long, cmsoType As String
ShtName = "Controls List" '/\Again matter of personal preference
'/\

On Error Resume Next
Set ws = ThisWorkbook.Sheets(ShtName)
On Error GoTo 0

If ws Is Nothing Then ThisWorkbook.Worksheets.Add.Name = ShtName
If ws Is Nothing Then Set ws = ThisWorkbook.Sheets(ShtName)
If ws.Index <> 1 Then ws.Move Before:=Sheets(1)
ws.Activate

With ws
If .[A1] = Empty Then
.[A1] = "ToolBar Name"
.[B1] = "Visibility"
.[C1] = "MsoBarType"
.[D1] = "# of Controls"
.[E1] = "MsoControlType"
.[F1] = "Cntrl Caption"
.[G1] = "Cntrl Id"
End If
If .[A2] = Empty Then StrtRW = 2 Else StrtRW = .[A1].End(xlDown).Row + 1
End With

For Each TLbar In Application.CommandBars
'/\Grab yourself 1 Toolbar outta the many you have (should have)
TLbrName = TLbar.Name
If TLbar.Visible = True Then TBVisble = "True" Else TBVisble = ""
TLbrCtrlCnt = TLbar.Controls.Count
TLbrTypes = TLbar.Type
'/\couldnt find Type any other way except as a LONG, what the heck would that mean?!!!
'/\well it means the following
Select Case TLbrTypes
Case 0
msoType = "msoBarTypeNormal"
Case 1
msoType = "msoBarTypeMenuBar"
Case 2
msoType = "msoBarTypePopup"
Case Is > 2
msoType = "I dunno"
End Select

For Each cTrl In TLbar.Controls
'/\Then get ready to chk the controls within that selected Toolbar
Cells(StrtRW, 1) = TLbrName
Cells(StrtRW, 2) = TBVisble
Cells(StrtRW, 3) = msoType
Cells(StrtRW, 4) = TLbrCtrlCnt
cTrlTypes = cTrl.Type
'/\Type again is given as a LONG,,,meaning a number
'/\I wanted it in words
Select Case cTrlTypes
Case 0
cmsoType = "msoControlActiveX"
Case 1
cmsoType = "msoControlButton"
Case 2
cmsoType = "msoControlButtonDropdown"
Case 3
cmsoType = "msoControlButtonPopup"
Case 4
cmsoType = "msoControlComboBox"
Case 5
cmsoType = "msoControlCustom"
Case 6
cmsoType = "msoControlDropdown"
Case 7
cmsoType = "msoControlEdit"
Case 8
cmsoType = "msoControlExpandingGrid"
Case 9
cmsoType = "msoControlGauge"
Case 10
cmsoType = "msoControlGenericDropdown"
Case 11
cmsoType = "msoControlGraphicCombo"
Case 12
cmsoType = "msoControlGraphicDropdown"
Case 13
cmsoType = "msoControlGraphicPopup"
Case 14
cmsoType = "msoControlGrid"
Case 15
cmsoType = "msoControlLabel"
Case 16
cmsoType = "msoControlOCXDropDown"
Case 17
cmsoType = "msoControlPane Pane control"
Case 18
cmsoType = "msoControlPopup"
Case 19
cmsoType = "msoControlSplitButtonMRUPopup"
Case 20
cmsoType = "msoControlSplitButtonPopup"
Case 21
cmsoType = "msoControlSplitDropdown"
Case 22
cmsoType = "msoControlS"
Case Is > 22
cmsoType = "I Dunno"
End Select
Cells(StrtRW, 5) = cmsoType
Cells(StrtRW, 6) = cTrl.Caption
Cells(StrtRW, 7) = cTrl.ID
StrtRW = StrtRW + 1
Next
Next
With ws
.[A:G].Columns.AutoFit
.Range(Cells(1, 1), Cells(1, 7)).Font.Bold = True
End With

End Sub

[/vba]

[Bob Phillips]

See my last reply.

[tdm100]

See my last reply.

lol
Oh believe my buddy, your code didn't go unnoticed by yours truly.
As I said earlier, before I even attempted to use it, I thought it was "Nifty"

I think it's one of the most unique short written codes I have ever seen presented in a forum.
It is of my opinion it would behoove any beginning to intermediate VBA coder to thoroughly examine your code contents and make sure they understand it.
Slick and to the point and at the same time very exploitive of the VBA program is how I would describe it.
(Though you might not think of it as exploitive but rather resourceful)
I would say recursive is an understatement!
HA!
But I couldn't think of an upper echelon word that would supersede recursive!

Unfortunately though, I believe those that would benefit the most from it, its gonna fly right past 'em in the absence of greater commentary.

Slick little code Dude

[tdm100]

btw,
what is ctlId = 265
and in case I still dont know what it is,,why was it singled out?

[Bob Phillips]

Oh believe my buddy, your code didn't go unnoticed by yours truly.

I was just making the point that I had already addressed the XL2000 issue with the code.

I would say recursive is an understatement!

I said recursive because the routine calls itself, not as a comment on any qualities of the code. It just seemed to flow nicely with Erdinc's statements.

[Bob Phillips]

btw,
what is ctlId = 265
and in case I still dont know what it is,,why was it singled out?

265 is the id for Worksheet menu Bar (in Excel 2002 on). I couldn't figure out a way of uniquely idenmtifying it using the type, so I settled on its id.