Use VBA to automate the population of a userform

[MGGDGG]

Hi there,

I'm looking to push a batch of data through a userform created using VBA. I was hoping I could use VBA itself to take data from a sheet and automatically enter it into the pre-built userform. However when I open the userform using my macro and attempt to populate it's fields using code in the same macro, nothing happens - I'm not sure whether my macro recognises the userform has been opened.

My question is, has anyone managed to create a macro which does this type of activity i.e. populates a userform (which has been built using VBA) which imitates a real life user entering data into that userform?

Many thanks in advance!

[sintek]

Welcome to the Forum...

Yes, we could provide you with 100's of solutions and none will work for you...
So, upload a sample file of exact file setup and explain what you are trying to achieve...and someone will provide the correct code for your setup...

See Top Yellow Banner

[MGGDGG]

Thanks for the reply,

I've attached an example of a very simple userform which takes an input value from a userform and copies it into a cell in excel.

In the VBA, I've included a macro (Module 2) which attempts to populate the userform with a value (instead of having to manually enter it in the form) but, after the userform pops up, it simply hangs and the value is not entered into the textbox.

I'm looking to understand how to amend the code such that the macro can successfully populate the value in the textbox and click 'OK' on the userform.

Thanks in advance.

[torachan]

Get rid of module2 and place the code (below) in the UserForm module.

Please Login or Register  to view this content.

[MGGDGG]

Thank you for this, worked a charm.

However, when I try to then get the code to automatically click the 'Ok' button it only half works (see attached); specifically it performs the action of populating the cell in excel but it doesn't then hide the userform as per the last step of the 'CommandButton1_Click' sub.

Does anyone know how I can edit the code to perform all actions in the 'CommandButton1_Click' sub so it behaves in exactly the same way as if you clicked the button manually.

Thanks

[sintek]

Cannot unload a Userform without it first being initialized...
What are you actually wanting to achieve...You still have not given more info to Post 2

[MGGDGG]

Hi there,

Please see below a walk through of my issue:

1. I've created a very basic userform that, when manually filled in, copies the entry onto an excel spreadsheet (see attached)
2. What I'm looking to do is run an automated test script which imitates the above behaviour, specifically:
a. Open up the userform
b. Fill in the textbox with a value (3 in the case of my example)
c. Click 'Ok' (as per the CommandButton1_Click sub the value would then copy into the excel spreadsheet and the userform would close)
3. In order to achieve this, I've added in the following sub to kick in when the form is initialised:

Public Sub UserForm_Initialize()

Application.Wait DateAdd("s", 2, Now)
UserForm1.TextBox1.Value = "3"
CommandButton1_Click

End Sub

4. However, when I run the button1_click() sub to kick off the automated script, it does everything apart from close down the userform at the end
5. On inspection it seems to only do half the commands in the CommandButton1_Click sub as it populates the excel sheet but doesn't then close down the userform
6. My question is, how do I adapt my code to close down the form after the excel sheet is populated by the automated script

Thanks

[sintek]

Like i said...the Userform is not finished initializing...so you cannot run a button housed on the userform

Why are you doing all of that if all you want to do is enter a value in a cell...
Why not just

Please Login or Register  to view this content.

[MGGDGG]

Thanks for the reply - I'm looking to do this as we essentially have an excel front end full of userforms which I want to run a set of automated test script against (i.e. for a batch of input tests, to check all userforms are working correctly). I provided a very cut down example of this for the forum to keep things simple.

Picking up on your point, how do I finish initializing the the Userform and so that my automated script can then successfully click the 'Ok' button?

[sintek]

Please Login or Register  to view this content.

Std module...

Please Login or Register  to view this content.

[MGGDGG]

Thank you for this - last question from me; is there any way to actually get the macro to click on the 'Ok' button rather than imitating the resulting actions? This may not be possible but wanted to check.

[sintek]

Please Login or Register  to view this content.

[MGGDGG]

Perfect, thank you.

[sintek]

Pleasure...If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.