Checking which menu item has been selected from a custom commandbar

Posted by Mudface on January 22, 2002 5:57 AM

I've set up a sheet to run a summary on a couple of other (quarterly) sheets. My code below adds the custom menu items to the command bar. I was wondering, rather than having a load of sub's for each quarter, whether there's a way of checking which of the menu items was selected. That way I could just have one sub and could assign variables depending on which quarter and year was chosen.

Sub AddMenus()

If CommandBars("Worksheet Menu Bar").Controls.Count > 10 Then _
Application.CommandBars("Worksheet Menu Bar").Controls(11).Delete

Set menuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, _
Before:=11, temporary:=True)
menuObject.Caption = "Choose Year"

Set menuItem = menuObject.Controls.Add(Type:=msoControlPopup)
menuItem.Caption = "2001"

For x = 1 To 2
Set subMenuItem = menuItem.Controls.Add(Type:=msoControlButton)
With subMenuItem
.Caption = "Quarter " & x + 2
.FaceId = 46 + x
.OnAction = "Quarter" & x + 2 & menuItem.Caption
End With
Next x

Set menuItem = menuObject.Controls.Add(Type:=msoControlPopup)
menuItem.Caption = "2002"

For x = 1 To 4
Set subMenuItem = menuItem.Controls.Add(Type:=msoControlButton)
With subMenuItem
.Caption = "Quarter " & x
.FaceId = 273 + x
.OnAction = "Quarter" & x & menuItem.Caption
End With
Next x

Set menuItem = menuObject.Controls.Add(Type:=msoControlPopup)
menuItem.Caption = "2003"

For x = 1 To 4
Set subMenuItem = menuItem.Controls.Add(Type:=msoControlButton)
With subMenuItem
.Caption = "Quarter " & x
.FaceId = 264 + x
.OnAction = "Quarter" & x & menuItem.Caption
End With
Next x

End Sub

' Example of the Quarter 3 2001 assigned sub

Sub Quarter32001()

QuarterStart = "1/7/01"
QuarterId = " third quarter of 2001 "

myYear = "2001"
myQuarter = "Q3"
myPath = "Q_3"

Call SelectionCheck

If Response = vbYes Then
Time1 = Time
Call OpenandCopy
End If

End Sub

Posted by Juan Pablo G. on January 22, 2002 6:20 AM

I've recently accomplished this using

Application.Caller

Try this example.

Option Explicit

Sub MakeBar()
Dim bar As CommandBar
Dim bt As CommandBarButton
Dim i As Integer

On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0

Set bar = Application.CommandBars.Add("MyBar", msoBarFloating, False, True)
With bar
For i = 1 To 3
Set bt = .Controls.Add(msoControlButton)
With bt
.Caption = "# " & i
.Style = msoButtonCaption
.OnAction = "Test"
End With
Next i
.Visible = True
End With
End Sub

Private Sub Test()
MsgBox CommandBars("MyBar").Controls(Application.Caller(1)).Caption
End Sub

First run "MakeBar" and click any of the buttons. The caption of the button will appear. Hope this gets you there.

By the way, Application.Caller is another way of making sure a macro IS NOT run from Alt - F8, check the help to see what i mean.

Juan Pablo G.

myYear = "2001" myQuarter = "Q3" myPath = "Q_3" Call SelectionCheck If Response = vbYes Then Time1 = Time Call OpenandCopy End If

Posted by Mudface on January 22, 2002 6:40 AM

Excellent! Thanks, Juan Pablo, I think I'll use the floating menu-bar, too, as it's more noticable when you open up the file. Thanks again,

Chris

myYear = "2001" myQuarter = "Q3" myPath = "Q_3" Call SelectionCheck If Response = vbYes Then Time1 = Time Call OpenandCopy End If