Can you use VBA to "click" a button that fires a macro?

[PlasticMonster]

Probably the wrong approach but to enable me to run some code before a button is clicked I want to be able to click a button via VBA code.

Basically I used the auto button wizard to make an add record button.

To ensure a shed loads of if checks and queries on the entered form data run I wanted to make this button invisible, then make a new button running code not embedded macros to run my checking module then click the original helpful add record button.

Even to me it sounds abit like programming gaffa tape but I have no idea what else to do and think if its possible it should work.... albeit not very elegantly.

 

[jdraw]

In plain English WHAT do you want to do?

 

[mdlueck]

Probably the wrong approach but to enable me to run some code before a button is clicked I want to be able to click a button via VBA code.

In order to be able to call a button click event from other VBA code, you must convert the event from Private to Public.

Usually I implement a separate event and make that one Public, and leave the button's click event default / private. Example...

[B]Private[/B] Sub btnPartAdd_Click()

  Call PartAdd_Click

End Sub

[B]Public[/B] Sub PartAdd_Click()
  On Error GoTo Err_PartAdd_Click

  'Worker code here...

Exit_PartAdd_Click:
  Exit Sub

Err_PartAdd_Click:
  Call errorhandler_MsgBox("Form: Form_parts, Subroutine: PartAdd_Click()")
  Resume Exit_PartAdd_Click

End Sub

 

[PlasticMonster]

Thanks for the replies guys.

Jdraw: I am trying to use VBA code to "virtually click" a button. This button however was created by the auto button wizard and thus uses macros not vba code so doesnt seem to have a vba _click event I can refer to.

Mdlueck: That looks a workable solution but I cant use the click event of the button as above because it fires a macro not a vba code event. I even tried to turn macros to vba as a get around but it would not allow me as in 2010 they seem to be embeded and will not convert.

 

[mdlueck]

I even tried to turn macros to vba as a get around but it would not allow me as in 2010 they seem to be embeded and will not convert.

So in 2010, the UI for wiring events to forms no longer provides selection of a Macro name vs an Event Procedure? See attached:

 

[PlasticMonster]

No you get macro, expression or code.

Ive currently maanged to make the buttons "authentically" with VBA now im trying to get them to do the checks I want

about 80% there now

 

[Pat Hartman]

I have to admit I am thoughly confused by your request but you can convert the embedded macro to VBA (in design view, select the button and use the button on the ribbon). Then you will be able to call the button's click event.

 

[PlasticMonster]

Apologies guys, I take full blame for the confusion.

Ive found a new way of doing what I wanted but to try and clarify (in case your curious and because your trying to help me)

I used the auto button wizard to make an add record button.

I wanted to do a check but only knew how to do it in VBA.

Because the auto button wizard makes a macro, I tried to convert it but got a message that said I couldent as it was embedded.

I decided if i couldent add the vba code i might be able to use code to press the button.

If I could press the button I thought I could have the button press follow my code.

Sorry for the confusion but ive manually coded the button now solved the problem that way. Im sure you'll tell me there was an easier way but after a few hours sitting at a computer with L plates on your chair you tend to accept whatever works (like parking miles from the supermarket doors when you start to drive...cos you wouldent need to reverse...)

 

[mdlueck]

I used the auto button wizard to make an add record button.

Aaahhh, that explains the weird things you were reporting then! I am glad you got it sorted out now.

 

[Pat Hartman]

There is a button on the ribbon that would have converted the embedded macro to VBA for you.