How to create a Macro to click a button

[156sting]

hey guys,

soo i want to create a macro which clicks a button in my form.

i tried doing Create sub macro and giving it the name ^6 (which is CTRL+6) and add the action RunCode and put the function name as btnCharge60Day_Click() (which is the were the code i want to run) but it doesnt work ?

what am i doing wrong ?

any and all help is greatly appreciated!

 

[ashleedawg]

You can call a button's subroutine from a macro, but there's probably a better way to accomplish whatever it is you're trying to do. Usually any code that you want to be "publicly accessible" would be moved to a module, where it can be called by any sub or form.

With more info, we could probably point you in a better direction. But for now...

If the button is named btnCharge60Day

- Go to the VBA for button's OnClick event and change the sub name from:

Private Sub btnCharge60Day_Click()

to

Public Sub btnCharge60Day_Click()

now the sub can be called from anywhere... but we can't call Sub's from a macro, only Functions [I never understood why not; anyone know?] so next create a Function that calls the Sub:

- Go to a module or create a new one (Create -> Module) and add a Function:

Function runCharge60Day()
Form_YOURFORMNAMEHERE.btnCharge60Day_Click
End Function

- Now create (or edit) your macro to call the function:

Create -> Macro
RunCode
Function Name: runCharge60Day()
Save & Close
​

This isn't technically "pushing the button"; Running the macro calls the function which calls the sub to run the code behind the button's OnClick event. A little convoluted huh? It will work but there's probably a better way.


LOL, my 13yo kid was just reading over my shoulder and now he's singing,

"She swallowed the cat to catch the bird,
she swallowed the bird to catch the spider,
she swallowed the spider to catch the fly,
she swallowed the fly to push the button..."

Yeah, he's kind of a smartass. But he's right, it's kinda like that.

 

[156sting]

ahslee dawg!! you my friend are a legend!

all you guys on this forum are soo helpful, i am learning soo much!

that is exactly what i want to do! its not a publicly accessible database, and the form that it runs thru only i use it!

many thanks again, i look forward to my next problem

 

[ashleedawg]

You're welcome, glad to help!


But to clarify -- you said it's not a "publicly accessible" database... if you're referring to my comment about changing the sub from Private to Public, you may have misunderstood:

• Public Sub - can be accessed from any module within your database
• Private Sub - can be accessed only from within the same module

There's no single line of code you can change that makes the database accessible to the general public population... although that would be handy!

 

[Galaxiom]

Function runCharge60Day()
Form_YOURFORMNAMEHERE.btnCharge60Day_Click
End Function

This is definitely not recommended.

This syntax makes the call via the module rather than the form. If the form is not open, a hidden instance of the form will be loaded to the Forms Collection.

Any call to a Method (AKA Public procedure) of another form should be via the Forms Collection.

Forms!YOURFORMNAMEHERE.btnCharge60Day_Click

This syntax will fail if the form is not already open but that is far better than the possibility of ending up with two indistinguishable instances of the same form open.

BTW, my preference, instead of making a button procedure Public, is to move the original button code into a Public procedure with a name that reflects what it does. This can be called externally as a Method as well as internally from the button.

It seems more logical to me and looks a lot better than having a Method with _Click in the name.