Rightclick menu - OnAction with conditions (1 Viewer)

[Acke]

Hi Guys,

I want to use one Function to trigger button action in the right-click menu conditioned by the button selected on the custom right-click menu.

The function that activates the right-click menu:

Const MyString As String ="MyMenuName"

Dim cbar As CommandBar
Dim bt as CommandBarButton

Set cbar = CommandBars.Add("MyMenuName", msoBarPopup,, False)
Set bt = cbar.Controls.Add

btCaption = "Button1"
bt.OnAction = "=DoSomething()"
bt.FaceId = 7468
bt.Tag = 1
btCaption = "Button2"
bt.OnAction = "=DoSomething()"
bt.FaceId = 7468
bt.Tag = 2

The Function "DoSomething()" should be used for both menu selections, and the activity should be determined by the "Tag".

Something like:

SelectCase bt.Tag
Case 1
action x
Case 2
action y
EndSelect

How should Function "DoSomething()" be designed?

Thanks!

 

[theDBguy]

Hi. If I understand it correct, but not sure it's possible, is probably to pass the Tag to the function. If so, DoSomething() should be declared to accept an argument. For example:

Public Function DoSomething(Tag As String) As Variant

Just a guess...

 

[Acke]

It looks as if you understand what I wan to do.

I am not sure how to write the rest of the Function "DoSomething". How should I make a connection between bt.Tag from Function that triggers the menu and Function "DoSomething()"?

Could you please write a sample?

 

[theDBguy]

It looks as if you understand what I wan to do.

I am not sure how to write the rest of the Function "DoSomething". How should I make a connection between bt.Tag from Function that triggers the menu and Function "DoSomething()"?

Could you please write a sample?

Okay, I'll give it a shot, but it might take some time.

 

[theDBguy]

Hi. I gave it a quick try and turns out it's not too difficult. However, I just noticed in your sample code that you're setting a Tag value for your right-click buttons. When I first read your post, I assumed you were referring to the Tag property of the control on the Form. So, just to clarify, which Tag value did you want to pass to the DoSomething() function? The Tag value you set in the context menu or the one you might set for the control on the form?

 

[Acke]

The Tag value from context menu.

If "button1" (option one in right-click menu) selected, do "action x". Using "bt.Tag1" as condition.

 

[theDBguy]

The Tag value from context menu.

If "button1" (option one in right-click menu) selected, do "action x". Using "bt.Tag1" as condition.

Okay, not knowing what your goal is, just on the surface, that should be easy enough. For example:

bt.OnAction = "=DoSomething(YourTagValueHere)"

 

[Acke]

Okay, not knowing what your goal is, just on the surface, that should be easy enough. For example:

bt.OnAction = "=DoSomething(YourTagValueHere)"

Where should this be listed? The function that triggers the contect menu, or Function DoSomething()?

 

[theDBguy]

Where should this be listed? The function that triggers the contect menu, or Function DoSomething()?

The first one. The context menu.

 

[Acke]

I am sorry, I still don't understand how to set the condition at Function "DoSomething". What should be listed after "Select Case" in the Function "DoSomehting"? Please see both codes as they are at this stage:

Function CreateMenu() ' Creates right-click menu

Const MyString As String ="MyMenuName"

Dim cbar As CommandBar
Dim bt as CommandBarButton

Set cbar = CommandBars.Add("MyMenuName", msoBarPopup,, False)
Set bt = cbar.Controls.Add

btCaption = "Button1"
bt.OnAction = "=DoSomething(1)"
bt.FaceId = 7468

btCaption = "Button2"
bt.OnAction = "=DoSomething(2)"
bt.FaceId = 7468

End Function



Public Function DoSomething(Tag As String) As Variant ' triggers the event depending on the right-click menu selection

Dim db As Database
Dim rs As Recordset
Dim frm As String
dim subf As String

frm = Screen.ActiveForm.Name
subf = Screen.ActiveControl.Parent.Name

Set db = CurrentDB
Set rs = db.Openrecordset("SELECT * FROM myTable WHERE Id =" & Forms(frm).Controls(subf).Form.myFormTextBox, dbOpenDynaset)
rs.edit

Select Case ??? >>>>>>What should I list here as a condition that relates to context menu selection? (right-click menu belongs to text control of the subfrom)<<<<<<

Case 1
rs!myTableField = 1

Case 2
rs!myTableField = 2

End Select

rs.update
rs.close
db.close

Forms(frm).Controls(subf).Form.Refresh
End Funcion

 

[theDBguy]

Select Case Tag

 

[smig]

One comment
I think you use function in the wrong way.

Normally you should use a Sub to do some action and use a Function when you want to get something back (Do a test and return the result of it)

There are some cases that you must call a Function even though it act as a Sub.
The CreateMenu() is probably not the case, while the DoSomething() is one of these cases

 

[Acke]

One comment
I think you use function in the wrong way.

Normally you should use a Sub to do some action and use a Function when you want to get something back (Do a test and return the result of it)

There are some cases that you must call a Function even though it act as a Sub.
The CreateMenu() is probably not the case, while the DoSomething() is one of these cases

Thanks for the comment. Not sure what you are trying to say. How would you organize codes in this case? I am not a professional programmer.