Understanding Menu Items and Building Them

[Tim Childs]

Hi

When building a sub-menu, how can I find out what the <Before> variable
should be where there may be other Temporary menu items already in the sub
menu before this is created - if say I want to have the sub-menu appear just
before Paste Special (say), how can I know which number that should be - I
am looking for a technique here not just a number for that instance

Hope that makes sense (hopefully!)

Thanks

Tim



With Application.CommandBars("Edit").Controls.Add(temporary:=True,
Before:=9)
.Tag = "PasteBanklineStatement"
On Error Resume Next
.Caption = "Paste Bankline Statement"
.OnAction = ThisWorkbook.Name & "!Pasting_Statement"
On Error GoTo 0
End With

[Jim Cone]

Tim,
You can use the Index number for the particular menu item...

Set objNew = .Controls("Paste &Special...")
If Not objNew is Nothing Then
lngIndex = objNew.Index
Else
lngIndex = .Controls.Count + 1
End If
Set objNew = .Controls.Add(Type:=msoControlButton, Before:=lngIndex)

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Tim Childs" <[email protected]> wrote in message
news:[email protected]...
Hi
When building a sub-menu, how can I find out what the <Before> variable
should be where there may be other Temporary menu items already in the sub
menu before this is created - if say I want to have the sub-menu appear just
before Paste Special (say), how can I know which number that should be - I
am looking for a technique here not just a number for that instance
Hope that makes sense (hopefully!)
Thanks
Tim

With Application.CommandBars("Edit").Controls.Add(temporary:=True,
Before:=9)
.Tag = "PasteBanklineStatement"
On Error Resume Next
.Caption = "Paste Bankline Statement"
.OnAction = ThisWorkbook.Name & "!Pasting_Statement"
On Error GoTo 0
End With

[Tim Childs]

Jim

many thanks for posting this - it looks to be just what I needed

Tim



"Jim Cone" <[email protected]> wrote in message
news:#[email protected]...
> Tim,
> You can use the Index number for the particular menu item...
>
> Set objNew = .Controls("Paste &Special...")
> If Not objNew is Nothing Then
> lngIndex = objNew.Index
> Else
> lngIndex = .Controls.Count + 1
> End If
> Set objNew = .Controls.Add(Type:=msoControlButton, Before:=lngIndex)
>
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Tim Childs" <[email protected]> wrote in message
> news:[email protected]...
> Hi
> When building a sub-menu, how can I find out what the <Before> variable
> should be where there may be other Temporary menu items already in the sub
> menu before this is created - if say I want to have the sub-menu appear
just
> before Paste Special (say), how can I know which number that should be - I
> am looking for a technique here not just a number for that instance
> Hope that makes sense (hopefully!)
> Thanks
> Tim
>
> With Application.CommandBars("Edit").Controls.Add(temporary:=True,
> Before:=9)
> .Tag = "PasteBanklineStatement"
> On Error Resume Next
> .Caption = "Paste Bankline Statement"
> .OnAction = ThisWorkbook.Name & "!Pasting_Statement"
> On Error GoTo 0
> End With