Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2005
    Posts
    7

    Unanswered: VBA Module - Toolbar and Button

    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.

    e_s_

  2. #2
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    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.

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Use the Commandbar Object to manipulate and access the toolbars. See Help for the full details

  4. #4
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    hope that's what you're lookin' 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

    does that help

  5. #5
    Join Date
    Mar 2005
    Posts
    7

    Solved

    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.

    e_s_

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    bachatero,

    I've tried your explanation for this and at step (4) the drop down does not list any sub procedures in the form or modules? I have a sub in the form called "cmdCallOpenExcel_Click"

    I typed this in and also tried "cmdCallOpenExcel_Click()" which when tested gives an error message.

    "<NameofDB> can't run the macro or callback function 'cmdCallOpenExcel_Click'.

    "Make sure the macro or function exists and takes the correct parameters."


    Right now as a work-around I have the 'OnOpen' event of the form set to run the process and close the form. The Toolbar button opens the form. I'd like to run the procedure directly from the button.

    Any clues? what am I missing?

    Thanks
    ~

    Bill

  7. #7
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    holá savbill

    you need to be in the customize mode .. then you get the named properties of a toolbar button, then - as previously pointed out - look for "on action"

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by bachatero
    you need to be in the customize mode .. then you get the named properties of a toolbar button, then - as previously pointed out - look for "on action"
    I guess I'm just stuck on this one Screen Shot Toolbar Properties
    ~

    Bill

  9. #9
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    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)
    docmd.openForm FormName)
    end sub

    then type "openForm" to the On Action prop and type "frmCallOpenExcel" to the Parameter prop.

  10. #10
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by bachatero
    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)
    docmd.openForm FormName)
    end sub

    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.

    Thanks for the Help

    ~
    ~

    Bill

  11. #11
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •