Pass data between UserForms

Question

Within Excel VBA I have a User Form similar to the following where the user enters an ID number and then the details are displayed on the user form:

Private Sub btnIDNo_Click()
Dim IDNo As Long
If txtIDNo.Text <> "" Then
    If IsNumeric(txtIDNo.Text) = True Then
        lblError.Caption = ""
        IDNo = txtIDNo.Text
        Worksheets("Details").Activate
        Range("B4").Select
        While ActiveCell.Value <> "" And ActiveCell.Value <> IDNo
            ActiveCell.Offset(1, 0).Select
        Wend
        If ActiveCell.Value = IDNo Then
            txtName.Value = ActiveCell.Offset(0, 1).Value
            txtPhone.Value = ActiveCell.Offset(0, 2).Value
        Else
            lblError.Caption = "Cannot find ID nummber"
        End If
    Else
        lblError.Caption = "Please enter the ID Number in numeric form"
    End If
End If
End Sub

On the Details User Form, I have an "Edit" button. Clicking the "Edit" button would open another user form where the user can change the details of that ID number, but obviously not the ID number itself. To do this, I need to pass the ID number from the Details User Form to the Edit User Form. Is there a way of doing this?

The bottom on the Show Details User Form to open the Edit User Form is similar to the following:

Private Sub CommandButton1_Click()
Dim IDNo As Long
If txtIDNo.Text <> "" Then
    If IsNumeric(txtIDNo.Text) = True Then
        lblError.Caption = ""
        IDNo= txtIDNo.Text
        ufmEditDetails.Show
        ufmShowDetails.Hide
    Else
        lblError.Caption = "Please enter the ID Number in numeric form"
    End If
Range("B4").Select
End If
End Sub

I have already looked at the following links but they don't seem to help:

http://www.mrexcel.com/forum/excel-questions/671964-visual-basic-applications-pass-variables-between-user-forms.html

http://gregmaxey.mvps.org/word_tip_pages/userform_pass_data.html

http://peltiertech.com/Excel/PropertyProcedures.html

Have you considered implementing your 2 current forms as a single form with a multipage control? You can put the fields currently on your first form on page 1 of the multi page, and the fields currently on your second form on page 2. You can make page 2 invisible by default and control within the code in the single form when you wish to display page 2 — Mark Moore
– Mark Moore, Commented Sep 11, 2015 at 10:03

Siddharth Rout · Accepted Answer · 2015-09-11 10:06:44Z

22

There are many many ways... Here are some...

Way 1

Declare a Public Variable in a Module
Assign to that variable in Userform1 and then launch Userform2. This variable will retain it's value. Example

In Userform1

Private Sub CommandButton1_Click()
    MyVal = "Sid"
    UserForm2.Show
End Sub

In Userform2

Private Sub CommandButton1_Click()
    MsgBox MyVal
End Sub

In Module

Public MyVal

Way 2

Use the .Tag property of the userform

In Userform1

Private Sub CommandButton1_Click()
    UserForm2.Tag = "Sid"
    UserForm2.Show
End Sub

In Userform2

Private Sub CommandButton1_Click()
    MsgBox Me.Tag
End Sub

Way 3

Add a Label in Userform2 and set it's visible property to False

In Userform1

Private Sub CommandButton1_Click()
    UserForm2.Label1.Caption = "Sid"
    UserForm2.Show
End Sub

In Userform2

Private Sub CommandButton1_Click()
    MsgBox Label1.Caption
End Sub

answered Sep 11, 2015 at 10:06

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DragonSamu Over a year ago

Wouldn't the most simple way be UserForm2.Txtbox.Text = UserForm1.txtIDNo.Text

Siddharth Rout Over a year ago

Yes it would be :) I answered the question "How do I pass data between User Forms?" As far as your answer is concerned, I have already demonstrated a similar concept in Way 3 :)

Adisak Anusornsrirung · Accepted Answer · 2015-09-11 10:06:21Z

2

There are serveral ways to solve this problem. The one that I use is declare global or public variable in module

Example:

Public commonVariable As String

then in userform you can assign or retrieve value from this variable. For example in userform1:

Private Sub btnIDNo_Click()
    commonVariable = "UserId"
End Sub

in UserForm2:

Private Sub CommandButton1_Click()
    me.txtIDNo.Text = commonVariable 
End Sub

answered Sep 11, 2015 at 10:06

Adisak Anusornsrirung

7008 silver badges14 bronze badges

Comments

DragonSamu · Accepted Answer · 2015-09-11 11:10:23Z

1

The most simplest way is:

UserForm2.TxtIDNo.Text = UserForm1.txtIDNo.Text

answered Sep 11, 2015 at 11:10

DragonSamu

1,16110 silver badges18 bronze badges

Collectives™ on Stack Overflow

Pass data between UserForms

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related