I created a commandBar
code is given below
when I rightclick the menu is invoked. But when I click on an item, my
function is not called (specified in the OnAction = ....)

I have another question
is there any specific place we write these codes?
when I tried

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
in a test workbook it gives errors saying, procedure call or argument is
wrong.
the same code works in my book. what am my missing to notice?

Thank you for the help
MVM


--------------- this is in standard module --------------
Public Sub CreateMyPopup()
If Not CommandBars("MyBar") Is Nothing Then
CommandBars("MyBar").Delete
End If

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
End Sub

---------------

--------------- thisworkbook -----fills the controls in the commandbar
Public Sub PopulateMyPopup()
Dim c As Range
Call SetRanges
Call CreateMyPopup
myPopup.Enabled = True
For Each c In AcctRange.Columns
Set myMnu = myPopup.Controls.Add(Type:=msoControlButton,
temporary:=True)
With myMnu
.Caption = c.Cells(1, 1).Value & " - " & c.Cells(3, 1).Value
' .DescriptionText = "MMMM"
.Tag = c.Cells(1, 1).Value
.Enabled = True
.Visible = True
' .OnAction = "GetCode(Expenses.Cells(16, 10)," & .Caption & ")"
.OnAction = "Expenses.Cells(16, 10).value = " & Left(.Caption,
1) & ")"
.Execute
End With
Next c
End Sub

---------------

I don't think you can set OnAction to a function with parameters.

OnAction property gets set to the Function Name only.
Use the Parameter property to specify a parameter for the function.


Use the ActionControl to determine what the Parameter is after the
commandbutton has been clicked.

Sub MyOwnOnAction()
MsgBox Application.CommandBars.ActionControl.Parameter
End Sub


I have a CommandBar example on my website: "Commandbar Buttons"


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"MVM" wrote in message
...
I created a commandBar
code is given below
when I rightclick the menu is invoked. But when I click on an item, my
function is not called (specified in the OnAction = ....)

I have another question
is there any specific place we write these codes?
when I tried

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
in a test workbook it gives errors saying, procedure call or argument is
wrong.
the same code works in my book. what am my missing to notice?

Thank you for the help
MVM


--------------- this is in standard module --------------
Public Sub CreateMyPopup()
If Not CommandBars("MyBar") Is Nothing Then
CommandBars("MyBar").Delete
End If

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
End Sub

---------------

--------------- thisworkbook -----fills the controls in the commandbar
Public Sub PopulateMyPopup()
Dim c As Range
Call SetRanges
Call CreateMyPopup
myPopup.Enabled = True
For Each c In AcctRange.Columns
Set myMnu = myPopup.Controls.Add(Type:=msoControlButton,
temporary:=True)
With myMnu
.Caption = c.Cells(1, 1).Value & " - " & c.Cells(3, 1).Value
' .DescriptionText = "MMMM"
.Tag = c.Cells(1, 1).Value
.Enabled = True
.Visible = True
' .OnAction = "GetCode(Expenses.Cells(16, 10)," & .Caption &
")"
.OnAction = "Expenses.Cells(16, 10).value = " & Left(.Caption,
1) & ")"
.Execute
End With
Next c
End Sub

---------------

Thank you Rob - that is great it worked.
Bob: I have the explicite option on all modules
I declared it globally.

the other question is still puzzles me.

MVM

"Rob van Gelder" wrote:

I don't think you can set OnAction to a function with parameters.

OnAction property gets set to the Function Name only.
Use the Parameter property to specify a parameter for the function.


Use the ActionControl to determine what the Parameter is after the
commandbutton has been clicked.

Sub MyOwnOnAction()
MsgBox Application.CommandBars.ActionControl.Parameter
End Sub


I have a CommandBar example on my website: "Commandbar Buttons"


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"MVM" wrote in message
...
I created a commandBar
code is given below
when I rightclick the menu is invoked. But when I click on an item, my
function is not called (specified in the OnAction = ....)

I have another question
is there any specific place we write these codes?
when I tried

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
in a test workbook it gives errors saying, procedure call or argument is
wrong.
the same code works in my book. what am my missing to notice?

Thank you for the help
MVM


--------------- this is in standard module --------------
Public Sub CreateMyPopup()
If Not CommandBars("MyBar") Is Nothing Then
CommandBars("MyBar").Delete
End If

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
End Sub

---------------

--------------- thisworkbook -----fills the controls in the commandbar
Public Sub PopulateMyPopup()
Dim c As Range
Call SetRanges
Call CreateMyPopup
myPopup.Enabled = True
For Each c In AcctRange.Columns
Set myMnu = myPopup.Controls.Add(Type:=msoControlButton,
temporary:=True)
With myMnu
.Caption = c.Cells(1, 1).Value & " - " & c.Cells(3, 1).Value
' .DescriptionText = "MMMM"
.Tag = c.Cells(1, 1).Value
.Enabled = True
.Visible = True
' .OnAction = "GetCode(Expenses.Cells(16, 10)," & .Caption &
")"
.OnAction = "Expenses.Cells(16, 10).value = " & Left(.Caption,
1) & ")"
.Execute
End With
Next c
End Sub

---------------

I'll put the code into a standard module. That's where most code goes.

Except workbook / worksheet event code.
Worksheet event code goes in worksheet module, Workbook event code goes in
workbook module.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"MVM" wrote in message
...
Thank you Rob - that is great it worked.
Bob: I have the explicite option on all modules
I declared it globally.

the other question is still puzzles me.

MVM

"Rob van Gelder" wrote:

I don't think you can set OnAction to a function with parameters.

OnAction property gets set to the Function Name only.
Use the Parameter property to specify a parameter for the function.


Use the ActionControl to determine what the Parameter is after the
commandbutton has been clicked.

Sub MyOwnOnAction()
MsgBox Application.CommandBars.ActionControl.Parameter
End Sub


I have a CommandBar example on my website: "Commandbar Buttons"


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"MVM" wrote in message
...
I created a commandBar
code is given below
when I rightclick the menu is invoked. But when I click on an item, my
function is not called (specified in the OnAction = ....)

I have another question
is there any specific place we write these codes?
when I tried

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
in a test workbook it gives errors saying, procedure call or argument
is
wrong.
the same code works in my book. what am my missing to notice?

Thank you for the help
MVM


--------------- this is in standard module --------------
Public Sub CreateMyPopup()
If Not CommandBars("MyBar") Is Nothing Then
CommandBars("MyBar").Delete
End If

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
End Sub

---------------

--------------- thisworkbook -----fills the controls in the commandbar
Public Sub PopulateMyPopup()
Dim c As Range
Call SetRanges
Call CreateMyPopup
myPopup.Enabled = True
For Each c In AcctRange.Columns
Set myMnu = myPopup.Controls.Add(Type:=msoControlButton,
temporary:=True)
With myMnu
.Caption = c.Cells(1, 1).Value & " - " & c.Cells(3, 1).Value
' .DescriptionText = "MMMM"
.Tag = c.Cells(1, 1).Value
.Enabled = True
.Visible = True
' .OnAction = "GetCode(Expenses.Cells(16, 10)," & .Caption &
")"
.OnAction = "Expenses.Cells(16, 10).value = " &
Left(.Caption,
1) & ")"
.Execute
End With
Next c
End Sub

---------------

I am not too clear where you are doing what, but it looks to me that you are
trying to add the button to the popup before you have created the popup.

Where is the mypopup variable declared? You would be wise to precede all
your modules with Option Explicit to force variable declaration.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MVM" wrote in message
...
I created a commandBar
code is given below
when I rightclick the menu is invoked. But when I click on an item, my
function is not called (specified in the OnAction = ....)

I have another question
is there any specific place we write these codes?
when I tried

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
in a test workbook it gives errors saying, procedure call or argument is
wrong.
the same code works in my book. what am my missing to notice?

Thank you for the help
MVM


--------------- this is in standard module --------------
Public Sub CreateMyPopup()
If Not CommandBars("MyBar") Is Nothing Then
CommandBars("MyBar").Delete
End If

Set myPopup = CommandBars.Add(Name:="MyBar", Position:=msoBarPopup)
End Sub

---------------

--------------- thisworkbook -----fills the controls in the commandbar
Public Sub PopulateMyPopup()
Dim c As Range
Call SetRanges
Call CreateMyPopup
myPopup.Enabled = True
For Each c In AcctRange.Columns
Set myMnu = myPopup.Controls.Add(Type:=msoControlButton,
temporary:=True)
With myMnu
.Caption = c.Cells(1, 1).Value & " - " & c.Cells(3, 1).Value
' .DescriptionText = "MMMM"
.Tag = c.Cells(1, 1).Value
.Enabled = True
.Visible = True
' .OnAction = "GetCode(Expenses.Cells(16, 10)," & .Caption &
")"
.OnAction = "Expenses.Cells(16, 10).value = " & Left(.Caption,
1) & ")"
.Execute
End With
Next c
End Sub

---------------