I have written some VBA code to link several files as tables in my database. I would like to create a custom toolbar with this module as a button on it for other users to have one click linking of files. It is possible with a macro, but with a VBA module? Any help on how to do this would be greatly appreciated.
It's possible. The fastest way to do it is to create your macro to do what you want it to do, then in the database window, right click on the macro >Save As../Export > Save as Visual Basic Module. This will create the code that you are looking for.
if you want to attach a vba sub (from within a module) to a menu bar that's fairly easy.
1. create custom menu bar (or enhance existing one)
2. Add Custom Control (File category) to your menu bar
3. Right click on Custom Control to get properties
4. Find the >On Action< property and type or select your sub name
5. that's all
6. you might pass a parameter to the sub using the parameter property
Thank you bachetero, your tip solved the problem. I only have one function in this module (the first I have ever writted in fact), and I had foolishly been putting the module name in the OnClick box. This makes much more sense! Thanks again.
exactly that's where you should be .. now type the name of the sub that opens your form frmCallOpenExcel at the "On Action"
or if you want to do it a bit more sophisticated write a sub
public sub openFom(FormName as string)
then type "openForm" to the On Action prop and type "frmCallOpenExcel" to the Parameter prop.
I have it working as advertised now. The problem I believe is the Sub Procedure to run must be located in the 'Modules' VB Directory. I was trying to run a sub in the Form Class module.
I was trying to eliminate calling a form to run this sub proc, which opens the Excel Application.
btw: if you just put the name of the form in the Parameter field that will open a form without having to call a procedure. Dragging a form command button from the Customize dialog to the toolbar sets this up.
could have told you that
class methods (name of subs and funcs within a class) are specific. you can only call them if you have an existing object of the named class using <classname>.<subname> (i don't know if there are static methods in vba). but you definitely can't call a sub that is part of a class (module) on its own.
thanks for the form open hint .. will try that next time