• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

Running a public form function (via shortcut menu)

I need some help with a shortcut menu that is being created via VBA.  I want it to run a function within an open form, but for some reason can't get it to work.

Here is the test code that I have written within a module to create the shortcut menu:

Public Function SCTest()

    Dim cmbShortcutMenu As office.CommandBar
    Dim ctlCBarControl As CommandBarControl
    Set cmbShortcutMenu = CommandBars.Add("Shortcut_Test", msoBarPopup, False, False)

'Show selected outcomes
    Set ctlCBarControl = cmbShortcutMenu.Controls.Add(msoControlButton)
    With ctlCBarControl
       .Caption = "Test"
       .OnAction = "=forms!frmSCTest.SC_Function()"
       .Tag = .Caption
    End With
    Set cmbShortcutMenu = Nothing

End Function

Open in new window


Within the form "SCTest", I have created the following function

Public function SC_Function()
     msgbox "Hello"
End function

Open in new window


I have added a Textbox, set the shortcut menu property and when I right-click, select "Test"  I'd like it to trigger SC_Function() - but it doesn't.  I'm guessing I have something wrong with the .onaction syntax, but haven't been able to solve it.
 
Any ideas what I'm doing wrong?
0
Andy Brown
Asked:
Andy Brown
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
Eric ShermanAccountant/DeveloperCommented:
You need to call you function in VBA code like below ...

Call SC_Function()


End If
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The OnAction syntax is:

=SC_Function()

and SC_Function must be  a Public Function in a Standard VBA Module, not behind a Form.
0
 
Andy BrownDeveloperAuthor Commented:
Hi guys - thanks for the help.

Is there any way I can get it to call the code that is within the form (rather than a module)?  Moving the existing code would take longer than it's worth.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
if you are going to use/call the function ONLY in this Form, you can place the code in the Form's module,
but remember that you can NOT use the function from any other form.
0
 
Andy BrownDeveloperAuthor Commented:
That's exactly what I wanted Rey - thank you.  However, I can't seem to get the syntax right (see code above).
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Do you have this in the OnAction

=SC_Function()

?

And again ... SC_Function() cannot be in code behind a Form .... must be in a Standard VBA Module.
However, from there ... you can call code behind a Form.
0
 
Andy BrownDeveloperAuthor Commented:
Ah - so if I create a function in the module, it can call the function behind the form?
0
 
Andy BrownDeveloperAuthor Commented:
That worked - thank you.
0
 
Rey Obrero (Capricorn1)Commented:
<SC_Function() cannot be in code behind a Form >  NOT true

try this sample db
SampleFormFunction.accdb
0
 
Andy BrownDeveloperAuthor Commented:
Thanks again Rey / everyone.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Rey ... I don't see any Menu Bar or Right Click menu ??
0
 
Rey Obrero (Capricorn1)Commented:
I just showed you that you can place a function in the form's module.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
But I'm talking about calling a Function in a Form Module from a Shortcut (aka Right Click) or legacy Menu Bar OnAction property.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now