Use data from one userform to formula in another

0
votes

Quite new to vba and have been trying to figure out how to use data from my first userform to my second userform.

let's call them userform1 and userform2

So in userform1, user will enter data for a, b, c ,and d. Upon clicking OK, userform2 will open:

Private Sub OK_Click()
     l = cdbl(a.value)+cdbl(b.value) 
     w = cdbl(c.value)+cdbl(d.value)

     userform1.hide
     userform2.show
End Sub

In userform2, i need the values of a and b (entered by user in userform1) to compute for x and y:

Private Sub OK_Click()
     x = cdbl(a.value)+cdbl(d.value) 
     y = cdbl(b.value)+cdbl(c.value)
End Sub

tried placing a placing a b c d l w x and y in a module and setting in Public but code still doesn't work. Error "object required"

Thanks very much in advance.

2
vbaexcel
Would x = cdbl(Userform1.a.value)+cdbl(Userform1.d.value) work? - CLR
Thanks! no error when i ran it but there still seems to be a problem as it doesnt give correct answer. also forgot to mention that i simplified my question to 2 userforms but userform1 can be any of 8. any suggestions how i can work that out? - G. Lab
What are a, b, x and y etc? Objects? Are they text boxes, scroll bar values or something like that? - CLR
textboxes. also figured out the answers part. still figuring out how to call data from different userforms. - G. Lab
Suggested readings Userforms and Class Modules. Another way is described here. Another a similar post - Storax

2 Answers

1
votes

A simple maybe not the best way is to declare variables a and b in the class modules. The better way might be to pass them via properties.

Code in Userform1

Option Explicit

Public a As Double
Public b As Double

Private Sub CommandButton1_Click()
    a = TextBox1.Value
    b = TextBox2.Value
    Me.Hide
End Sub

Code in Userform 2

Option Explicit

Public x As Double
Public y As Double

Private Sub CommandButton1_Click()
    TextBox1.Value = x + y
End Sub

And you can test it like that

Sub Demo()

Dim f1 As New UserForm1
Dim f2 As New UserForm2

    f1.Show
    f2.x = f1.a
    f2.y = f1.b
    f2.Show

End Sub

PS No checks or whatsover if the values entered in the textboxes are really valid.

0
votes

a rough way

1) declare some Public variables of Double type

hence put this a the top of any module of your choice in the project

   Public aValue As Double, bValue As Double, cValue As Double, dValue As Double

2) change your code as follows

UserForm1

    Option Explicit

    Private Sub OK_Click()
        Dim l As Double, w As Double

        aValue = CDbl(a.Value)
        bValue = CDbl(b.Value)
        cValue = CDbl(c.Value)
        dValue = CDbl(d.Value)

        l = aValue + bValue
        w = cValue + dValue

        Me.Hide
        With UserForm2
            .Show
        End With
    End Sub

UserForm2

    Private Sub OK_Click()
        Dim x As Double, y As Double

         x = aValue + dValue
         y = bValue + cValue
    End Sub

and the likes ...