I can't even comprehend how to attack this so I wanted to put it out there and see if something sparks.
I have a user form that dynamically pulls row values based on a users comboBox selection. and I have set a section for user notes based on the dynamic row value and places it in column L. Of course it works awesome and the variables all sync well...however my boss now wants the notes to pop up on a second user form when the user click "Update" (ok no problem, 2nd user form piece of cake), but the row is dynamically named in userform one, how can I pass that to the note's text box in Userform two and keep the variables true...am I a block head?
Userform 1
Option Explicit
'Declare module level variables
Dim bNormalProcess As Boolean
Dim bcol2Valid As Boolean
Dim temp As String
Private Sub UserForm_Initialize()
'Initialize variables
Me.updateButton.Enabled = False
bcol2Valid = False
bNormalProcess = True
Dim rngColumn1 As Range
Dim ws As Worksheet
' Populate combo box list that will expand as the range expands.
Set ws = Worksheets("Master_list")
For Each rngColumn1 In ws.Range("_Col1")
Me.cboCol1.AddItem rngColumn1.Value
Next rngColumn1
lbl_exit:
Exit Sub
End Sub
Private Sub cboCol1_Change()
Dim ws As Worksheet
Set ws = Worksheets("Master_list")
' set the text box values with corresponding places on the spreadsheet based on combo box.
With Me
.TextBox1.Value = ws.Cells(.cboCol1.ListIndex + 5, 2)
.TextBox2.Value = ws.Cells(.cboCol1.ListIndex + 5, 3)
.TextBox3.Value = ws.Cells(.cboCol1.ListIndex + 5, 4)
.TextBox4.Value = ws.Cells(.cboCol1.ListIndex + 5, 5)
.TextBox5.Value = ws.Cells(.cboCol1.ListIndex + 5, 6)
.TextBox6.Value = ws.Cells(.cboCol1.ListIndex + 5, 7)
.TextBox7.Value = ws.Cells(.cboCol1.ListIndex + 5, 8)
.TextBox8.Value = ws.Cells(.cboCol1.ListIndex + 5, 9)
.TextBox9.Value = ws.Cells(.cboCol1.ListIndex + 5, 10)
'Store the address values for each control in the .Tag attribute
'(False, False) stores the address as 'A1' instead of '$A$1'
.cboCol1.Tag = ws.Cells(.cboCol1.ListIndex + 5, 1).Address(False, False)
.TextBox1.Tag = ws.Cells(.cboCol1.ListIndex + 5, 2).Address(False, False)
.TextBox2.Tag = ws.Cells(.cboCol1.ListIndex + 5, 3).Address(False, False)
.TextBox3.Tag = ws.Cells(.cboCol1.ListIndex + 5, 4).Address(False, False)
.TextBox4.Tag = ws.Cells(.cboCol1.ListIndex + 5, 5).Address(False, False)
.TextBox5.Tag = ws.Cells(.cboCol1.ListIndex + 5, 6).Address(False, False)
.TextBox6.Tag = ws.Cells(.cboCol1.ListIndex + 5, 7).Address(False, False)
.TextBox7.Tag = ws.Cells(.cboCol1.ListIndex + 5, 8).Address(False, False)
.TextBox8.Tag = ws.Cells(.cboCol1.ListIndex + 5, 9).Address(False, False)
.TextBox9.Tag = ws.Cells(.cboCol1.ListIndex + 5, 10).Address(False, False)
temp = CStr(.cboCol1.ListIndex + 5)
TextBox1.SetFocus
End With
End Sub
Private Sub cancelButton_Click()
Unload UserForm1
MsgBox "No Changed Made", vbCritical
End Sub
Private Sub updateButton_Click()
Call PutUserForm1DataBackInWorksheet
Unload UserForm1
'UserForm2.Show
'Sheets("Master_list").Activate
End Sub
Sub PutUserForm1DataBackInWorksheet()
Dim ws As Worksheet
Set ws = Worksheets("Master_list")
Dim sAddress As String
Dim sValue As String
'Get the Worksheet Cell Address
'Get the value from the UserForm
'Put the value in the Worksheet
'ComboBox1
sAddress = UserForm1.cboCol1.Tag
sValue = Trim(UserForm1.cboCol1.Text)
ws.Range(sAddress).Value = sValue
'TextBox1
sAddress = UserForm1.TextBox1.Tag
sValue = Trim(UserForm1.TextBox1.Text)
ws.Range(sAddress).Value = sValue
'TextBox2
sAddress = UserForm1.TextBox2.Tag
sValue = Trim(UserForm1.TextBox2.Text)
ws.Range(sAddress).Value = sValue
'TextBox3
sAddress = UserForm1.TextBox3.Tag
sValue = Trim(UserForm1.TextBox3.Text)
ws.Range(sAddress).Value = sValue
'TextBox4
sAddress = UserForm1.TextBox4.Tag
sValue = Trim(UserForm1.TextBox4.Text)
ws.Range(sAddress).Value = sValue
'TextBox5
sAddress = UserForm1.TextBox5.Tag
sValue = Trim(UserForm1.TextBox5.Text)
ws.Range(sAddress).Value = sValue
'TextBox6
sAddress = UserForm1.TextBox6.Tag
sValue = Trim(UserForm1.TextBox6.Text)
ws.Range(sAddress).Value = sValue
'TextBox6
sAddress = UserForm1.TextBox7.Tag
sValue = Trim(UserForm1.TextBox7.Text)
ws.Range(sAddress).Value = sValue
'TextBox7
sAddress = UserForm1.TextBox8.Tag
sValue = Trim(UserForm1.TextBox8.Text)
ws.Range(sAddress).Value = sValue
'TextBox8
sAddress = UserForm1.TextBox9.Tag
sValue = Trim(UserForm1.TextBox9.Text)
ws.Range(sAddress).Value = sValue
'Get dynamic row and target it at Column K to datestamp it
sAddress = "k" + temp
sValue = DateTime.Now
ws.Range(sAddress).Value = sValue
'Get dyanic Row and target it at Column L to allow for user notes
sAddress = "l" + temp
sValue = Trim(UserForm1.TextBox10.Text)
ws.Range(sAddress).Value = sValue
Set ws = Nothing
End Sub
Private Sub textBox1_Enter()
Call textBox1_Change
lbl_exit:
Exit Sub
End Sub
Private Sub textBox1_Change()
'In all cases Assume invalid on change
bcol2Valid = False
With Me
'display instructional label text.
.instructionsLabel.Caption = "Change your 6 digit Historical Provider Number. Place Zeroes to the left of the number to make it six digits."
.instructionsLabel.ForeColor = vbBlue
.instructionsLabel.Font.Bold = True
'Clear Alert text
.alertsLabel.Caption = " "
.alertsLabel.BackColor = vbButtonFace
'Does this change validate this field?
If Len(.TextBox1) = 6 Then
bcol2Valid = True
.instructionsLabel.Caption = "Historical Provider Number, which should be a 6 digit number."
.instructionsLabel.ForeColor = vbBlue
.instructionsLabel.Font.Bold = True
End If
End With
'Does this change validate or invalidate this form?
ValidateForm
lbl_exit:
Exit Sub
End Sub
Private Sub textBox1_Exit(ByVal cancel As MSForms.ReturnBoolean)
cancel = True
'keep focus on the invalid field
If Not bcol2Valid = True Then
cancel = True
'Changes the alert box to show what error may be and highlight the background and text
Me.alertsLabel.Caption = "The Historical Provider Number Provided is incorrect" & vbCr + vbCr & "Please reenter the 6 digit number."
Me.alertsLabel.ForeColor = vbRed
Me.alertsLabel.BackColor = vbYellow
Me.alertsLabel.Font.Bold = True
End If
'Checks to make sure the box was not left balnk
If TextBox1.Value = "" Then
MsgBox "This entry cannot be left blank."
End If
lbl_exit:
Exit Sub
End Sub
Sub ValidateForm()
Dim bValid As Boolean
'true when all variable multiples are true
bValid = bcol2Valid
If bValid Then
Me.updateButton.Enabled = True
Else
Me.updateButton.Enabled = False
End If
lbl_exit:
Exit Sub
End Sub
Master List v2.63.xls
Bookmarks