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
To copy a form from one workbook to another, open both, then find them in the VB Editor's Project Explorer window. Drag the userform from one to the other. To create a new form, right click on the project in the VB Editor's Project Explorer, choose Insert > UserForm.
Approach: User can Select DMD number in the first column and Click 'Update' button. Same Userform will be filled with the selected row data and shown to the user to make changes. User will click the submit button to update the existing record.
There are many many ways... Here are some...
Way 1
Public
Variable in a ModuleIn 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With