Pre-populate TextBox with targeted cell entries for each active row

[Groovicles]

Hi everyone,

I have the following code:

Private Sub CommandButton1_Click()
Dim RowCount As Long
Dim ctl As Control
    With Me
        .TextBox1.Value = Cells(ActiveCell.Row, "A").Value
        .TextBox2.Value = Cells(ActiveCell.Row, "B").Value
    End With
    If Me.ComboBox1.Value = "" Then
        MsgBox "Please Select Employee Status.", vbExclamation, "Entry Form"
        Me.ComboBox1.SetFocus
    End If
    If Me.ComboBox2.Value = "" Then
        MsgBox "Please Select Licensed Rep Status.", vbExclamation, "Entry Form"
        Me.ComboBox2.SetFocus
    End If
    If Me.ComboBox3.Value = "" Then
        MsgBox "Please Select Language Preference.", vbExclamation, "Entry Form"
        Me.ComboBox3.SetFocus
    End If
    If Me.ComboBox4.Value = "" Then
        MsgBox "Please Select Current Role Experience.", vbExclamation, "Entry Form"
        Me.ComboBox4.SetFocus
    End If
    If Me.TextBox3.Value = "" Then
        MsgBox "Please Enter Post #.", vbExclamation, "Entry Form"
        Me.TextBox3.SetFocus
    End If
    If Me.TextBox4.Value = "" Then
        MsgBox "Please Enter Portfolio #.", vbExclamation, "Entry Form"
        Me.TextBox4.SetFocus
    End If
        RowCount = Worksheets("Info Request").Range("G" & Rows.Count).End(3)(2).Row
        With Worksheets("Info Request")
            .Cells(RowCount, 7).Value = Me.ComboBox4.Value
            .Cells(RowCount, 8).Value = Me.ComboBox1.Value
            .Cells(RowCount, 9).Value = Me.ComboBox2.Value
            .Cells(RowCount, 10).Value = Me.TextBox3.Value
            .Cells(RowCount, 11).Value = Me.TextBox4.Value
            .Cells(RowCount, 12).Value = Me.TextBox5.Value
            .Cells(RowCount, 13).Value = Me.ComboBox3.Value
        End With
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    Next ctl
End Sub
Private Sub CommandButton2_Click()
    Unload Me
End Sub
Private Sub Userform_Initialize()
With Me
    .TextBox1.Value = Cells(ActiveCell.Row, "A").Value
    .TextBox2.Value = Cells(ActiveCell.Row, "B").Value
End With
With ComboBox1
    .AddItem "Full Time"
    .AddItem "Part Time"
End With
With ComboBox2
    .AddItem "MFDA"
    .AddItem "MFDA < 90 Days"
    .AddItem "IIROC"
End With
With ComboBox3
    .AddItem "English"
    .AddItem "French"
End With
With ComboBox4
    .AddItem "<3 Months"
    .AddItem "3 Months - 1 Year"
    .AddItem "1 Year - 4 Years"
    .AddItem "Over 4 Years"
End With
End Sub

What I'm trying to get the userform "UnitEntryform" to do is populate TextBox1 and TextBox2 with the results in columns A and B of the template for each active row selected from column G. Right now, the first entry the user makes is pre-populated with the corresponding results found in columns A and B in the appropriate TextBoxes. However, when the user press Save Employee so that they move to the next active row, the TextBox1 and 2 does not show the results in A and B in the appropriate active row.

Anyway I can have the results in columns A and B populated in TextBox 1 and 2 for the appropriate row? I would attach a sample template but I can't seem to see where to do it.

Thanks everyone!!

 

[dmt32]

Hi try moving this part of your code:

With Me
        .TextBox1.Value = Cells(ActiveCell.Row, "A").Value
        .TextBox2.Value = Cells(ActiveCell.Row, "B").Value
    End With

to the very bottom of your procedure i.e just before End Sub
and see if that does what you want.

Dave

 

[Groovicles]

Hi try moving this part of your code:

With Me
        .TextBox1.Value = Cells(ActiveCell.Row, "A").Value
        .TextBox2.Value = Cells(ActiveCell.Row, "B").Value
    End With

to the very bottom of your procedure i.e just before End Sub
and see if that does what you want.

Dave

Hi dmt

I have those lines of code n both subs. Would I move them both down to the end?

Thanks,

 

[dmt32]

in your CommandButton1_Click procedure.

Dave

 

[royUK]

as you use those lines in two places, remove them and insert a new sub procedure. Then call that procedure when required

Option Explicit


Dim oWs As Worksheet


Private Sub CommandButton1_Click()
    Set oWs = Worksheets("Info Request")
    Dim RowCount As Long
    Dim ctl As MSForms.Control


    With Me
        If .ComboBox1.Value = "" Then
            MsgBox "Please Select Employee Status.", vbExclamation, "Entry Form"
            .ComboBox1.SetFocus
        End If
        If .ComboBox2.Value = "" Then
            MsgBox "Please Select Licensed Rep Status.", vbExclamation, "Entry Form"
            .ComboBox2.SetFocus
        End If
        If .ComboBox3.Value = "" Then
            MsgBox "Please Select Language Preference.", vbExclamation, "Entry Form"
            .ComboBox3.SetFocus
        End If
        If .ComboBox4.Value = "" Then
            MsgBox "Please Select Current Role Experience.", vbExclamation, "Entry Form"
            .ComboBox4.SetFocus
        End If
        If .TextBox3.Value = "" Then
            MsgBox "Please Enter Post #.", vbExclamation, "Entry Form"
            .TextBox3.SetFocus
        End If
        If .TextBox4.Value = "" Then
            MsgBox "Please Enter Portfolio #.", vbExclamation, "Entry Form"
            .TextBox4.SetFocus
        End If
        RowCount = oWs.Range("G" & oWs.Rows.Count).End(3)(2).Row
        With Worksheets("Info Request")
            oWs.Cells(RowCount, 7).Value = .ComboBox4.Value
            oWs.Cells(RowCount, 8).Value = .ComboBox1.Value
            oWs.Cells(RowCount, 9).Value = .ComboBox2.Value
            oWs.Cells(RowCount, 10).Value = .TextBox3.Value
            oWs.Cells(RowCount, 11).Value = .TextBox4.Value
            oWs.Cells(RowCount, 12).Value = .TextBox5.Value
            oWs.Cells(RowCount, 13).Value = .ComboBox3.Value
        End With
        For Each ctl In .Controls
            If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
                ctl.Value = ""
            ElseIf TypeName(ctl) = "CheckBox" Then
                ctl.Value = False
            End If
        Next ctl
    End With


    Update
End Sub
Private Sub CommandButton2_Click()
    Unload Me
End Sub
Private Sub Userform_Initialize()
    With Me
        .ComboBox1.List = Array("Full Time", "Part Time")
        .ComboBox2.List = Array("MFDA", "MFDA < 90 Days", "IIROC")
        .ComboBox3.List = Array("English", "French")
        .ComboBox4.List = Array("<3 Months", "3 Months - 1 Year", "1 Year - 4 Years", "Over 4 Years")
    End With
    Update
End Sub
Private Sub Update()
    Me.TextBox1.Value = oWs.Cells(ActiveCell.Row, "A").Value
    Me.TextBox2.Value = oWs.Cells(ActiveCell.Row, "B").Value
End Sub