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

Groovicles

Board Regular
Joined
Nov 27, 2013
Messages
52
Hi everyone,

I have the following code:

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!!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
Hi try moving this part of your code:

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

Board Regular
Joined
Nov 27, 2013
Messages
52
Hi try moving this part of your code:

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
in your CommandButton1_Click procedure.

Dave
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
as you use those lines in two places, remove them and insert a new sub procedure. Then call that procedure when required

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top