CODEWITHSUNDEEP
excelvba
james1395
james1395

Reputation: 239

How to open and run a userform?

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:

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.

Upvotes: 0

Views: 9691

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

@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

The answer:

I created 2 WB and just this simple code worked for me ... however: you may need to change the settings of the trust center.

Book1 Module: (the WB with Userform1 holding TextBox1 and CommandButton1)

Option Explicit

Public Function getUF()
  Set getUF = UserForm1
End Function

Book2 Module:

Option Explicit

Public ExtUF As Variant

Sub the_UF()
  Workbooks.Open "Book1.xlsm"
  Set ExtUF = Application.Run("Book1.xlsm!getUF") 'get the Form

  Debug.Print ExtUF.TextBox1.Value  'check old value
  ExtUF.TextBox1.Value = "dada"     'change it
  Debug.Print ExtUF.TextBox1.Value  'check for new value

  ExtUF.CommandButton1.Value = True 'hit the button
  ExtUF.Show                        'show the form
   Stop 'to check the userform
  ExtUF.Hide                        'hide it again
End Sub

Now just run the_UF and check for functionality. If everything does work, adopt it to your code the way you need it.

If you have any questions, just ask ;)

Upvotes: 0

DragonSamu
DragonSamu

Reputation: 1163

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) )

Upvotes: 0

Related Questions