Userform data entry into excel

[austin397]

Hello all,

I am attempting to have my userform enter data into certain cells on my spreadsheet. I need it to enter the data from a text box into a cell. The cell that it goes into depends on the data selected from a list box.

So if in the list box I select the option of "8605", and in the text box is the data "52" then I need it to look at Column A and if it find a match in column A that is "8605", then enter "52" in the cell of the same row and in column D.

In this instance it would find "8605" in cell A193. So in cell D193 I need it to enter "52".

Please help!

 

[My Aswer Is This]

Assuming your listbox is named "Listbox1"
Assuming your Textbox is named "Textbox1"
Modify names if needed.
Use this script:

Put this script in your listbox
When you click on a value in the listbox the script will run.

Private Sub ListBox1_Click()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim c As Range
Dim ans As Long
ans = ListBox1.Value
For Each c In Range("A1:A" & Lastrow)
If c.Value = ans Then c.Offset(, 3).Value = TextBox1.Value
Next
End Sub

 

[austin397]

This one worked perfectly, thank you!

Assuming your listbox is named "Listbox1"
Assuming your Textbox is named "Textbox1"
Modify names if needed.
Use this script:

Put this script in your listbox
When you click on a value in the listbox the script will run.

Private Sub ListBox1_Click()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim c As Range
Dim ans As Long
ans = ListBox1.Value
For Each c In Range("A1:A" & Lastrow)
If c.Value = ans Then c.Offset(, 3).Value = TextBox1.Value
Next
End Sub

 

[dssJones]

Not as fancy as Mr. Roderick, but it takes the bull by the horns

Private Sub CommandButton1_Click()
    Dim rowNbr As Long
    Dim foundItem As String
    If TextBox1.Text = vbNullString Then
        MsgBox "Please Populate Textbox"
        Exit Sub
    End If
    If searchFor = vbNullString Then
        MsgBox "Please Select Item from Listbox"
        Exit Sub
    End If
    Do
        rowNbr = rowNbr + 1
        If ActiveSheet.Cells(rowNbr, 1).Value = searchFor Then
            ActiveSheet.Cells(rowNbr, 4).Value = TextBox1.Text
            Exit Sub
        End If
    Loop While ActiveSheet.Cells(rowNbr, 1).Value <> vbNullString
End Sub

 

[dssJones]

All that is required is to update the loop so that it searches 300 records

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

This one worked perfectly, thank you!

 

[dssJones]

Forgot this gem:

Private Sub ListBox1_Click()
    searchFor = ListBox1.Text
End Sub

This bulletproofs your code against users that do not select from the listbox.

 

[austin397]

Thank you dssJones,

While I'm sure that your code does work, I couldn't quite get it to do what was desired. It didnt give any errors but it also didnt input the data from the textbox.

I thank you for your reply though!