Is it possible to use a macro to initiate a form click event?

[bdmangum]

There is one part in my program where I would like to load a form and then auto-initiate a commandbutton click event. The reason I would like to do it this way is because there is only one instance where I would need to do this, the rest of the time I would simply allow the user to select the button.

Is this possible to do?

I know I could simply create a module, insert the same code as the button, then run the macro. However, I'm curious if I can do it the way I listed above. Thoughts?

 

[RonRepp]

Yes.

Button1_Click()  'Where Button1 is the name of your button and there is actually a click event behind it.  In other words, use the event name.

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[bdmangum]

Thanks for the response Ron. I ran into some trouble implementing your code though. I created a test file to check if it works. Here's what I have:

Sheet 1 change event which should run click event

Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(Target.Row, Target.Column).Value = 5 Then
    UserForm1.CommandButton1_Click() = True
End If

End Sub

Commandbutton1.click event code

Private Sub CommandButton1_Click()

MsgBox "Clicked", vbOKOnly, "Test"

End Sub

I receive an error on the italics part in the first code sction. The error says: "Compile Error: Method or Data memeber not found."

I also inserted the click event call into the userform intialize event to see if the code you posted could only be used in the same form as the click event, but it still gave me the same error.

What am I doing incorrectly?

 

[combo]

You can either assign 'True' to a hidden 'Value' property (this is what you actually try to do, but using wrong object reference), or extend event procedure visibility so that it will become visible outside the form.

combo

 

[SkipVought]

Hi,

It might be better coding to code a procedure that you call from either the click event of from anoter procedure, rather than calling the click event.

Skip,

_[red][/red]

 

[RonRepp]

Skip is right (as usual). However, looking at your code I see one problem:

Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(Target.Row, Target.Column).Value = 5 Then
    UserForm1.CommandButton1_Click() = True
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Cells(Target.Row, Target.Column).Value = 5 Then
    UserForm1.CommandButton1_Click() 
End If

End Sub

The button click event is not a boolean value.

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.