Step 2: Code the button
The code for animating the menu is quite simple. There is, however,
one little trick. You must declare a public variable, of type object, to
which you assign the command bar object. The declaration is as simple as
Public objPopup As Object
Once this variable has been declared, enter the
following code on the Click event of your command button:
Private Sub cmdReports_Click()
On Error Resume Next
Set objPopup = CommandBars("mnuReports")
Set objPopup = Nothing
That is all there is to the code. The real work is in creating
the menus, and the objects that are called by the menus. The next step
discusses some of the things you can do, and some things you cannot do.
Step 3: Get fancy
I may lose my guru license for this, but I am going to suggest, nay
even recommend, that you code this next part using Macros. I know, they
are wicked and evil, but it is the only way I could get the popup menu to
launch a piece of code. You should, of course, write the code using
a Function in a standard module, but once it is working correctly, you need to
create a macro that launches it.
For example, my code includes a function to output the contents of a
table to text and open it with Notepad. The function looks like this:
Public Function ExportTableToText(ByVal sTable As String) As Boolean
On Error Resume Next
Dim sFile As String, sThisMDB As String, sOpen As String
Const q As String * 1 = """"
sThisMDB = CurrentDb.Name
sFile = Left(sThisMDB, InStrRev(sThisMDB, "\")) & sTable & ".txt"
If Dir(sFile) <> "" Then Kill sFile
DoCmd.TransferText acExportDelim, , sTable, sFile, True
sOpen = "notepad.exe " & q & sFile & q
Shell sOpen, vbNormalFocus
That code works great, but to launch it from the popup menu, it will have to
be put into a new macro. The following image shows the three functions
that are available from the Functions button on my sample application. I
simply named the macro, set its action to RunCode and supplied the name and
arguments of the public function described above. Once the macro is
saved, it becomes available to the popup menu from the All Macros category.
The first screen shot of this
article shows it all: many functions available from one button
click. There are times you will implement this because you need the real
estate, other times just because it's slick. In fact, as I wrote this
article it struck me how many different things you could do with this method
and I am certain I have overlooked some really valuable implementation.
If you think of something I missed, drop me a note. In addition, if you
figure out how to execute a function directly from a menu option without using
a macro, I would love to hear how you did it.
See All Articles by Columnist Danny J. Lesandrini