Running VB code directly from a Menu Bar

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I have a custom menu bar.

Is there any way of running a VBA function directly or do I have to have a
macro to do so.
If I do have to have a macro is there any way to use one, all purpose macro?

Noone of my functions expects parameters.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Jonathan:

Right-click on your custom menu item to bring up the Properties option
(after doing the normal View-Toolbars-Customize stuff as if you were
editing the menu item).

Select Properties.

In the OnAction box put:
=FunctionName()

The equals sign is needed. The parenthesis are needed. It has the be
a function, not a sub (as far as I know, for reasons I don't know).

-Matt
 
I would say that 90% of my custom menu bars call, and run my VBA code.


All you need to do is make the code (a function) public, and then simply
place the function name in the buttons on-action event code.

Further, likely often you will have specific code to a particular form, and
once again, you simply declare those functions (in that form) as public.

The syntax to call the code then is:

=YourFunctionName()

Often, (if not most of the time), you code you call will need to pick up
some information about he current screen etc. So, my code most of the time
starts out, and grabs the current screen name. I use:

Public Function AskInvoicePrint()

Dim tblgroupid As Long
Dim frmActive As Form

Set frmActive = Screen.ActiveForm

tblgroupid = frmActive.frmMainClientB.Form!ID

If frmActive.InvoiceNumber = 0 Then
frmActive.InvoiceNumber = nextinvoice
frmActive.Refresh
End If

DoCmd.OpenForm "guiInvoicePrint", , , "id = " & tblgroupid

End Function

The above is code that the invoice print button runs. note how I right away
pick up the active form. After that, I can easily ref the forms object as if
the code was running much like the code would if put behind a button on the
form. In the above example, I also check if a invoice number has been
generated before printing. And, the Refresh forces a disk write if in fact I
do change the invoice number. And, in addition the above clip also passes
the currently selected sub-form item that the invoice print form needs.

Also, if the code you write is for the particular form, then as mentioned,
there is no need to pick up the active screen...and you can use me! as you
always used.

If you want to see some sample menu bars, and why I use them, you can read
the following:

http://www.attcanada.net/~kallal.msn/Articles/UseAbility/UserFriendly.htm
 
Back
Top