I wrote some code within a UserForm that does xyz when a command button in the UserForm is clicked. I then created a macro that runs the code in the UserForm using the statement "UserForm1.Show". I then added a custom button to a toolbar that the user can click to run this macro. The result is the user clicks a custom button on a toolbar and the UserForm appears, instructing the user to click a command button that will cause xyz to happen.

My question is how do I get xyz to happen without requiring the user to click a command button on the UserForm I want xyz to be carried out as soon as the custom button on the toolbar is clicked. The intermediate step of clicking a command button is not needed. Does it have something to do with how the Sub that carries out xyz is named Consider the following code;

Private Sub Label1_Click()
If InStr(1, Selection, "dog") Then
Label1.Caption = "Found an occurrence of the word dog"
Label1.Caption = "Didn't find an occurrence of the word dog"
End If
End Sub

How could I alter this code so the click on the label is not required for the text search to be carried out I want the user to be able to click the custom button on the toolbar and the Userform (or a MessageBox) would immediately appear with the result of the search. Instead of the Sub being named Label1_Click(), should it be called something like (at a wild guess) Label1_JustDoIt()

Re: Executing code automatically

Andy Pope


Create your xyz macro in a standard code module. You can then assign it to the OnAction property of the toolbar button.

You can also, if needed, call it from a userform event, such as a button click. Or assign the macro to any object that supports the OnAction property.

As for the name of routine, and this covers your other post on the subject.

Label1_Click() would normally suggest the routine is in fact an event routine. Controls on a userform or from the Control toolbox have events. The naming of the event is automatic and is a combination of control name and event. So for example the Label1 control will also have a MouseMove event called, Label1_MouseMove. The subroutine header will also contain arguments which information is passed to when the event occurs.

What can be confusing is if you assign a macro to an object, such as a shape, or a forms control. Then the default routine name is again the object name plus _Click. The difference is for these you can change the routine that is run to any macro that is displayed in the macro dialog list. (And other routines under some circumstances but that is getting off topic).

Changing the name of Click event of a userform or activex control will at worst raise an error and at best do nothing. As the default event will be re created when required.