MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create click event on another userform while executing event from another userform

Posted by Jason on February 08, 2002 5:25 AM

I've programmed myself into a jam here.
I have a macro that opens another workbook, populates a few cells then saves and closes it. The problem is the workbook I'm opening has an open event brings up a userform that requires one of the command buttons clicked.
Is there a way I can put an automatic 'click' in the code?

Posted by Mudface on February 08, 2002 6:10 AM

Try: -

UserForm1.CommandButton1.Value = True

with your form and button names substituted to fire the click event. Strangely Excel VB doesn't give the Value Intellisense option for the CommandButton, but it seems to work regardless.

Posted by Jason on February 08, 2002 6:38 AM

Can't compile. Variable not defined
Workbook to be opened has the userform
Any ideas?

Posted by Mudface on February 08, 2002 8:32 AM

Best I can come up with is a workaround, but you'll need access to the code of the workbook with the form. If so, put the following in the first workbook's Open event: -

Windows(1).Caption = "Kill Form"

And the following before your code to show the form in the second workbook: -

Application.ScreenUpdating = False
For Each wb In Workbooks
If ActiveWindow.Caption = "Kill Form" Then Application.ScreenUpdating = True: Exit Sub
Next wb
Application.ScreenUpdating = False

Sorry if this is of no help.

Posted by Mudface on February 08, 2002 8:36 AM

And of course the last Application.ScreenUpdating should = True, not False.