MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Automate UserForm with Macro

  Asked By: Rose    Date: Dec 23    Category: MS Office    Views: 3209

I am not sure if I can explain what I am asking, let me try.

I am using a Excel VBA for engineering application, in it I make
several UserForms for somebody to choose Open / Close / Cancel.

So, the user will start the first macro button, say the Trigger, then
in the middle, then he will see a Userform and he has to choose the
open/close/cancel buttons in the UserForm.

Now, I would like to make another macro to simulate the action that is
taken by the user.

I have tried to record the steps but the recorder just record
something like this,

Sub automation()
Application.Run "Sheet1.xls!Trigger"
End Sub

All the input into the Userform is not simply not recorded.



3 Answers Found

Answer #1    Answered By: Frances Parker     Answered On: Dec 23

I am not sure I understand your question.

Are you trying to automate  something to happen when the user  presses one
of your buttons? When you are designing your form, if you double click
on the button  it will open  the code window for the particular button
that you just double clicked on. In there, you can now gather the
values of any of your textboxes and put them onto your spreadsheet.

Answer #2    Answered By: Adalgisa Fischer     Answered On: Dec 23

Yes, you get to the point I am asking, that I need a macro  to simulate
somebody clicking the button  on the UserForm.

I make the userform, so I know the code standing for each buttons,

But, I still have no clue how to automate  a reply to a userform  since
once the userform appears on the screen, all the vba  will be paused
until somebody clicks one of the buttons.

Anyway, must say thanks for your expert advice.

Something about my excel  VBA, this is a program about operating
pipeline with various valves / pumps. The user  has to open  several
valves one by one until he open all the valves from the source to the
final loading point. No calcuation but just full of shapes and
logical decision.

Now I want to have another macro to simulate the whole sequence of

Let me disclose part of my VBA,

Sub Valve01()
UnFreeze 'unprotect the excel graphic
BoxTitle = "Outlet Valve01"
ActiveSheet.Shapes("Group 1009").Select
Set valve = Selection.ShapeRange
ActiveSheet.Shapes("Group 1051").Select
Set outlet = Selection.ShapeRange
ActiveSheet.Shapes("oval 104").Select
Set tank = Selection.ShapeRange
Call TankValveOperate(valve, outlet, tank)
Freeze 'protect the graphic
End Sub

Sub TankValveOperate(valve, outlet, tank)
ValveInput.Show 'userform
If openvalve = 1 Then _
Call opentankvalve(valve, outlet, tank) Else _
Call closetankvalve(valve, outlet, tank)
End Sub

The Userform has the following codes:

Private Sub OpenVV_Click()
Application.ScreenUpdating = False
openvalve = 1
End Sub
Private Sub CloseVV_Click()
Application.ScreenUpdating = False
openvalve = 0
End Sub
Private Sub CancelVV_Click()
Application.ScreenUpdating = False
Freeze 'end the macro with protection again
End Sub
Private Sub UserForm_Initialize()
Application.ScreenUpdating = True
TankName.Value = BoxTitle 'changing the textbox heading
End Sub

Answer #3    Answered By: Meenakshi Khochar     Answered On: Dec 23

You should be able to use "OnTime" to generate a timed delay and you can use
that to click the required button.

Didn't find what you were looking for? Find more on Automate UserForm with Macro Or get search suggestion and latest updates.