Making a combobox of available Marcos to apply to current form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a lot of presaved queries that are expressed through marcos. Is there
a way to make a combobox that lists these Marcos and runs them when selected?
Thanks,
MarcTA
 
Hi, Marc.
Is there
a way to make a combobox that lists these Marcos and runs them when selected?
Click to expand...

Yes. Create a new query and paste the following SQL statement into the SQL
View pane:

SELECT [Name]
FROM MSysObjects
WHERE (([Type] = -32766) AND (Flags = 0))
ORDER BY [Name];

Save this query as qryMacroNames. Create a combo box on your form and set
the Row Source Type Property to Table/Query. Set the Row Source Property to
qryMacroNames. Set Limit To List Property to Yes. Create an After Update
event for this combo box. Try the following syntax:

' * * * * Start Code * * * *

Private Sub cboMacros_AfterUpdate()

On Error GoTo ErrHandler

DoCmd.RunMacro Me!cboMacros.Value

Exit Sub

ErrHandler:

MsgBox "Error in cboMacros_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

.... where cboMacros is the name of the combo box. Save the form and open it
in Form View. Select an item in the list box to run the macro.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
in message:
I have a lot of presaved queries that are expressed through marcos. Is there
a way to make a combobox that lists these Marcos and runs them when selected?
Click to expand...

Gunny has provided some nice code that will work perfectly, but I
wonder if you want any internal macro group names as well. If you
do, that will require a LOT more hoops to jump. I have some code
that will do that if need be.

If, however, you just need the macro object names, then use Gunny's
code and just ignore me.
:-)
 
Back
Top