How to open and run a userform?

Question

I have a userform on an Excel file called "userform":

Private Sub add1_Change()
End Sub

Private Sub add2_Change()
End Sub

Private Sub Calc_Click()
Result.Value = Val(add1.Value) + Val(add2.Value)
End Sub

This userform takes the value from the user and adds them together and shows the result in a textbox.

I want to create a new macro in another workbook named "input". The macro in this workbook should open the userform workbook, enter values in the textbox add1 and add2, then run the userform calculate button.

What I've tried thus far:

Set add1.value to extract a value from say, cell A1, and similarly for add2.value.
Then I created a macro on the input workbook to change the values in cells A1 and A2.

The problem from here is I don't know how to open the userform and click calculate.

Ideally, I would like a macro which opens the userform, enters the data and hits calculate then closes the userform - Rather than editing the userform itself.

Why use the userform at all for that? The point of a form is to get input from the user. If you aren't doing that, it doesn't make a lot of sense. — Rory
having something like this Public Sub getUF(a): Set a = UserForm1: End Sub in the wb with the userform, allows to catch that object directly... simply run getUF MyUFVariable and then use MyUFVariable exactly the same way like you would with the userform... — Dirk Reichel
@Rory I agree with your point, but I have a much bigger useform than the one shown and it would be easier to do it this way rather than breakdown the userform from scratch. It's just what I'm required to do for now. — james1395
@DirkReichel could you elaborate a bit more on this? I've added what you said, but say I wanted to change the value on the add1 textbox how would I call it? Right now, I have this: 'Sub userform() Dim a As Integer Dim b As Integer a = Cells(1, 2) b = Cells(2, 2) Workbooks.Open (ThisWorkbook.Path & "\userform.xlsm") Application.Run "userform.xlsm!Calc" End Sub' the calc macro just opens up the userform, I don't know how to actually "input" data or hit calculate — james1395
@james1395 you want to get Input from a Worksheet, do a calculation and then place it somewhere? What else does your UserForm do to not want to change now to a pure VBA code option instead of unneeded UserForm's — DragonSamu

DragonSamu DragonSamu · Accepted Answer · 2016-08-16T11:07:48

You could add the 2 values in the UserForm in this way(its slightly different then you try to do it now):

You use your current code to open the UserForm:

Sub userform()
    Workbooks.Open (ThisWorkbook.Path & "\userform.xlsm")
    Application.Run "userform.xlsm!Calc"
End Sub

As shown above you don't assign any values this will happen in your userform.xlsm Workbook

Below is the code you put into the sub Initialize of your UserForm:

Private Sub UserForm_Initialize()

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = Workbooks("input.xlsx")
    Set ws = wb.Worksheets("Input")

    Dim i as Integer
    Dim k as Integer

    UserForm.add1.Value = ws.Range("A2").Value
    UserForm.add2.Value = ws.Range("B2").value
    UserForm.calc.Value = val(UserForm.add1.Value) + val(UserForm.add2.Value)

End Sub

As shown above calc is changed to a Textbox, therefor you don't need to click a button its directly done when the UserForm is loaded.

You could also use a Label instead of a Textbox.

the code would then change to:

UserForm.calc.Caption = Str( val(UserForm.add1.Value) + val(UserForm.add2.Value) )

How to open and run a userform?

2 Answers