Solved: Userform;TextBox:Update Data

[sengsara]

Can userform textbox update data to worksheet?
Please someone help with this sample
Thank again...

[Bob Phillips]

Private Sub UserForm_Activate()
Dim LastRow As Long
Dim coll As Collection
Dim itm As Variant

With Worksheets("Data")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set coll = New Collection
On Error Resume Next
For I = 2 To LastRow
coll.Add .Cells(I, "A").Value, CStr(.Cells(I, "A").Value)
Next I
On Error GoTo 0
For Each itm In coll
Me.ComboBox1.AddItem itm
Next itm
Me.ComboBox2.ColumnCount = 1
End With
End Sub

[georgiboy]

I thought rather than creating a collection you might just as well add the item to the combobox where you was creating the collection, like this...

Private Sub UserForm_Activate()
Dim LastRow As Long

With Worksheets("Data")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For I = 2 To LastRow
Me.ComboBox1.AddItem .Cells(I, "A").Value
Next I
Me.ComboBox2.ColumnCount = 1
End With
End Sub

[sengsara]

Thanks Xld and Georgiboy two code is work but can get data in combobox2 it's ok and how about commandbutton1 click to update data
can macro do that?

[Bob Phillips]

Private Sub ComboBox1_Change()
Dim LastRow As Long
Dim NextRow As Long
With Worksheets("Data")
Me.ComboBox2.Clear
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
NextRow = MatchRow
Me.ComboBox2.AddItem .Cells(NextRow, "B").Value
ComboBox2.ListIndex = 0
End With
End Sub

Private Sub ComboBox2_Change()
Dim LastRow As Long
Dim NextRow As Long
With Worksheets("Data")
NextRow = MatchRow
Me.TextBox1.Value = Cells(NextRow, "C").Value
Me.TextBox2.Value = Cells(NextRow, "D").Value
Me.TextBox3.Value = Cells(NextRow, "E").Value
Me.TextBox4.Value = Cells(NextRow, "F").Value
End With
End Sub

Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim NextRow As Long
With Worksheets("Data")
NextRow = MatchRow
Cells(NextRow, "C").Value = Me.TextBox1.Value
Cells(NextRow, "D").Value = Me.TextBox2.Value
Cells(NextRow, "E").Value = Me.TextBox3.Value
Cells(NextRow, "F").Value = Me.TextBox4.Value
End With
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub


Private Sub UserForm_Activate()
Dim LastRow As Long
Dim coll As Collection
Dim itm As Variant

With Worksheets("Data")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set coll = New Collection
On Error Resume Next
For I = 2 To LastRow
coll.Add .Cells(I, "A").Value, CStr(.Cells(I, "A").Value)
Next I
On Error GoTo 0
For Each itm In coll
Me.ComboBox1.AddItem itm
Next itm
Me.ComboBox2.ColumnCount = 1
End With
End Sub

Private Function MatchRow() As Long
On Error Resume Next
MatchRow = Application.Match(Val(Me.ComboBox1.Value), Worksheets("Data").Columns(1), 0)
If MatchRow > 0 Then Exit Function
MatchRow = Application.Match(Me.ComboBox1.Value, Worksheets("Data").Columns(1), 0)
On Error GoTo 0
End Function

[sengsara]

Xld you really know what am looking for
i don't know how to say
you're great....cool
thank you very much....
i love it...

[sengsara]

xld or someone please see attach file
there's a problem
thank for all your help

[Bob Phillips]

Do you want to try giving us a clue as to what the problem might be?

[Benzadeus]

Try changing

With Worksheets("Data")


into


With Sheets("Data")

[sengsara]

thank Benzadeus
i change "worksheets("Data") with Sheets("Data") but there is no effect still can not show Size line 2 ,3,4 from A1001

xld i change the code

from

Private Function MatchRow() As Long
On Error Resume Next
MatchRow = Application.Match(Val(Me.ComboBox1.Value), Worksheets("Data").Columns(4), 0)
If MatchRow > 0 Then Exit Function
MatchRow = Application.Match(Me.ComboBox1.Value, Worksheets("Data").Columns(4), 0)
On Error GoTo 0
End Function

to

Private Function MatchRow() As Long
On Error Resume Next
MatchRow = Application.Match(Val(Me.ComboBox1.Value), Worksheets("Data").Columns(4), 1)
If MatchRow > 0 Then Exit Function
MatchRow = Application.Match(Me.ComboBox1.Value, Worksheets("Data").Columns(4), 1)
On Error GoTo 0
End Function

change columns(4),0 to column(4),1

if am use columns(4),0 this show the first line from every code ( select A1001 and the size will be 1-3 )
if am use columns(4),1 this show the last line from every code ( select A1001 and the size will be S-L )

how to change or to make all line effective so user can update or change the data from all line

A1001 have 4 line
A1002 have 2 line
A1003 have 3 line
A1004 have 3 line
A1005 have 1 line
A1006 have 3 line
A1007 have 2 line

thank for all your help

[mdmackillop]

This corrrects the combobox error but the returned detail is incorrect.
Private Sub ComboBox1_Change()
Dim LastRow As Long
Dim NextRow As Long
Dim rw As Long, i As Long

With Worksheets("Data")
rw = .Cells(Rows.Count, "D").End(xlUp).Row
Me.ComboBox2.Clear
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
For i = 2 To rw
If .Cells(i, "D") = ComboBox1 Then Me.ComboBox2.AddItem .Cells(i, "E").Value
Next
End With
End Sub

[sengsara]

yes Mdmackillop the code is work detail not work (line2 ,3,and 4 )
the size of the task is it 10 rows/searches
thank you Mdmackillop