Send CommandBarControl Tag as Parameter to Another Procedure

R

RyanH

I have a custom menu tab that has several control buttons and comboboxes. I
want to send the Tag Property to the procedure below and then call the
assigned procedure.

Note: This sub below is saved in an Add-In workbook which is used as a
reference for my "Global Schedule" workbook.

' this procedure is called by the 2003 Menu Controls
Public Sub OnAction2003Calls(strControlTag As String)

SubName = "OnAction2003Calls"
On Error GoTo ErrorHandler

Select Case strControlTag
Case "btn1": Call PrintReadySchedule
Case "btn2": Call PrintSalesSchedule
Case "btn3": Call PrintDeptSchedules
Case "btn4": Call ImportMacolaData
Case "btn5": Call SendToArchive
Case "btn6": Call SortbyItemNumber
Case "btn7": Call ReadySchedule
Case "btn8": Call SalesSchedule
Case "btn9": Call StartCompile

Case "cbo1": Call ChangeGlobalView
Case "cbo2": Call ChangeDeptViews
End Select

Exit Sub

'********************************

ErrorHandler:
Call ErrorHandler(SubName)

End Sub

'This is how I create the menu and buttons
Private Sub CreateAdTechMenu()

SubName = "CreateAdTechMenu"

' this AdTech menu setup is for version 2003 and earlier
If Application.Version > 11 Then Exit Sub

Dim MenuObject As CommandBarPopup
Dim MenuItem As Object

' make sure this menu is not currently there
Call DeleteMenu

' add Ad Tech tab in worksheet menu bar
With Application.CommandBars(1)
Set MenuObject = .Controls.Add(Type:=msoControlPopup,
Before:=.Controls.Count + 1, Temporary:=True)
End With
MenuObject.Caption = "AdTech"

' Import Macola Data menu item
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "Import Macola Data"
.FaceId = 1015
.OnAction = "OnAction2003Calls"
.Tag = "btn1"
End With

End Sub
 
J

Jim Rech

You do not need to 'send' anything. Just do this:

Public Sub OnAction2003Calls()
Select Case CommandBars.ActionControl.Tag
Case "btn1": Call PrintReadySchedule
Case "btn2": Call PrintSalesSchedule

--
Jim
|I have a custom menu tab that has several control buttons and comboboxes.
I
| want to send the Tag Property to the procedure below and then call the
| assigned procedure.
|
| Note: This sub below is saved in an Add-In workbook which is used as a
| reference for my "Global Schedule" workbook.
|
| ' this procedure is called by the 2003 Menu Controls
| Public Sub OnAction2003Calls(strControlTag As String)
|
| SubName = "OnAction2003Calls"
| On Error GoTo ErrorHandler
|
| Select Case strControlTag
| Case "btn1": Call PrintReadySchedule
| Case "btn2": Call PrintSalesSchedule
| Case "btn3": Call PrintDeptSchedules
| Case "btn4": Call ImportMacolaData
| Case "btn5": Call SendToArchive
| Case "btn6": Call SortbyItemNumber
| Case "btn7": Call ReadySchedule
| Case "btn8": Call SalesSchedule
| Case "btn9": Call StartCompile
|
| Case "cbo1": Call ChangeGlobalView
| Case "cbo2": Call ChangeDeptViews
| End Select
|
| Exit Sub
|
| '********************************
|
| ErrorHandler:
| Call ErrorHandler(SubName)
|
| End Sub
|
| 'This is how I create the menu and buttons
| Private Sub CreateAdTechMenu()
|
| SubName = "CreateAdTechMenu"
|
| ' this AdTech menu setup is for version 2003 and earlier
| If Application.Version > 11 Then Exit Sub
|
| Dim MenuObject As CommandBarPopup
| Dim MenuItem As Object
|
| ' make sure this menu is not currently there
| Call DeleteMenu
|
| ' add Ad Tech tab in worksheet menu bar
| With Application.CommandBars(1)
| Set MenuObject = .Controls.Add(Type:=msoControlPopup,
| Before:=.Controls.Count + 1, Temporary:=True)
| End With
| MenuObject.Caption = "AdTech"
|
| ' Import Macola Data menu item
| Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
| With MenuItem
| .Caption = "Import Macola Data"
| .FaceId = 1015
| .OnAction = "OnAction2003Calls"
| .Tag = "btn1"
| End With
|
| End Sub
| --
| Cheers,
| Ryan
 
Top