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.

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,
open/close/cancel.

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
operation.

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
ValveInput.Hide
openvalve = 1
End Sub
Private Sub CloseVV_Click()
Application.ScreenUpdating = False
ValveInput.Hide
openvalve = 0
End Sub
Private Sub CancelVV_Click()
Application.ScreenUpdating = False
ValveInput.Hide
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

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