Populate textboxes based on row number

[christianbiker]

Hi all...I am wondering if there is a way to populate textboxes in a user form based on the data contained in a row specified by the user. For example...

From within a userform, a user will specify a row # from a spreadsheet using textboxrow (i.e. row "13"). Once specified, the user will then click on a command button that will populate various textboxes in the userform with column values specified row. As an example:

• Column A populates textbox1
• Column B populates textbox2
• Column C populates textbox3
• etc., etc., etc.

If there is no data in the row, I would also like to end the sub and warn the user via msgbox that no data exists in that row.

Thanks in advance for any assistance!!!!

 

[DanteAmor]

Try:
Change the 3 for the amount of textbox you have in your user form in these lines:

n = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, 3)))
...
For i = 1 To 3

Private Sub CommandButton1_Click()
  Dim i As Long, n As Long, r As Long
 
  If textboxrow.Value = "" Or Not IsNumeric(textboxrow.Value) Then
    MsgBox "Enter a valid number"
    Exit Sub
  End If
  r = textboxrow.Value
  n = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, 3)))
  If n = 0 Then
    MsgBox "no data exists in that row", vbExclamation
    Exit Sub
  End If
 
  For i = 1 To 3
    Controls("TextBox" & i).Value = Cells(r, i).Value
  Next
End Sub

 

[AlphaFrog]

Private Sub CommandButton1_Click()
    'TextBox4 is the user input for the row numbar
    
    Dim MyRow As Variant
    
    MyRow = TextBox4.Value
    
    If Not IsNumeric(MyRow) Then
        MsgBox "Please enter a row number in TextBox4", vbExclamation, "Invalid row entry."
    ElseIf Application.WorksheetFunction.CountA(Rows(Val(MyRow)).Range("A1:C1")) = 0 Then
        MsgBox "Row " & Val(MyRow) & " is empty. Please enter a valid row number.", vbExclamation, "Invalid row number"
    Else
        TextBox1.Value = Range("A" & Val(MyRow)).Value
        TextBox2.Value = Range("B" & Val(MyRow)).Value
        TextBox3.Value = Range("C" & Val(MyRow)).Value
    End If
End Sub

 

[christianbiker]

Private Sub CommandButton1_Click()
    'TextBox4 is the user input for the row numbar
   
    Dim MyRow As Variant
   
    MyRow = TextBox4.Value
   
    If Not IsNumeric(MyRow) Then
        MsgBox "Please enter a row number in TextBox4", vbExclamation, "Invalid row entry."
    ElseIf Application.WorksheetFunction.CountA(Rows(Val(MyRow)).Range("A1:C1")) = 0 Then
        MsgBox "Row " & Val(MyRow) & " is empty. Please enter a valid row number.", vbExclamation, "Invalid row number"
    Else
        TextBox1.Value = Range("A" & Val(MyRow)).Value
        TextBox2.Value = Range("B" & Val(MyRow)).Value
        TextBox3.Value = Range("C" & Val(MyRow)).Value
    End If
End Sub

Thanks AlphaFrog...this works amazing and is exactly what I needed. Much appreciated!!!!!

 

[christianbiker]

Try:
Change the 3 for the amount of textbox you have in your user form in these lines:

n = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, 3)))
...
For i = 1 To 3

Private Sub CommandButton1_Click()
  Dim i As Long, n As Long, r As Long
 
  If textboxrow.Value = "" Or Not IsNumeric(textboxrow.Value) Then
    MsgBox "Enter a valid number"
    Exit Sub
  End If
  r = textboxrow.Value
  n = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, 3)))
  If n = 0 Then
    MsgBox "no data exists in that row", vbExclamation
    Exit Sub
  End If
 
  For i = 1 To 3
    Controls("TextBox" & i).Value = Cells(r, i).Value
  Next
End Sub

Thanks Dante...haven't had a chance to test but you have helped me before and everything is always spot on!!!