Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Running a public form function (via shortcut menu)

Posted on 2016-10-25
13
28 Views
Last Modified: 2016-10-25
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
Comment
Question by:Andy Brown
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41858795
You need to call you function in VBA code like below ...

Call SC_Function()


End If
0
 
LVL 75
ID: 41858932
The OnAction syntax is:

=SC_Function()

and SC_Function must be  a Public Function in a Standard VBA Module, not behind a Form.
0
 

Author Comment

by:Andy Brown
ID: 41859005
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41859028
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
 

Author Comment

by:Andy Brown
ID: 41859032
That's exactly what I wanted Rey - thank you.  However, I can't seem to get the syntax right (see code above).
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 41859071
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
 

Author Comment

by:Andy Brown
ID: 41859081
Ah - so if I create a function in the module, it can call the function behind the form?
0
 

Author Closing Comment

by:Andy Brown
ID: 41859087
That worked - thank you.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859095
<SC_Function() cannot be in code behind a Form >  NOT true

try this sample db
SampleFormFunction.accdb
0
 

Author Comment

by:Andy Brown
ID: 41859106
Thanks again Rey / everyone.
0
 
LVL 75
ID: 41859107
Rey ... I don't see any Menu Bar or Right Click menu ??
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41859116
I just showed you that you can place a function in the form's module.
0
 
LVL 75
ID: 41859130
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question