Select one row with same ID in userform listbox of quantity edit.

[mir994stan]

Hello everyone, i almost completed my workbook mission but i run into new problem, and i can t find solution anywhere on internet. So i decided to ask community for help. On one sheet i have userform which i use when i need to return amount of some matterial to main storage from production. Every product in my list have its own uniqe ID, and when i issue whole amount of that product all works perfect. But problem appears when i need to do issue of partial ammounts. For example: i have 500 units of some product and i need to issue today 300 units, and the couple days later i need to issue the rest, 200 units. I can t sum those ammounts in same row, that part is good and it all works like intended. I create 2 separate record with same ID and different ammounts or same depending on needs from production. The real problem is when guys from production needs to return certan amount of that product that they don t need any more. I created userform that when u enter ID in TextBox7 all data with that ID are shown in listbox. When u double click for wanted record u can change values of that record in textboxes. I do it all right, but values changes on all record that have same ID. My Question is: Is it possible to edit only record that is selected in listbox? Screenshot of workbook is in links below:
screen shot: Capture
As u can see i have two record with same ID (35) and i need to set value of record ID 35 in row 40 to zero but then i dbl clikc on that record in listbox and chage its value i change values on both records, bcs of the same ID.... can i somehow change only one record in that i select?
Any help is welcome. Thanks in advance!
If its of some good, i use this code to search for record on sheet with data:

Private Sub CommandButton8_Click()
'--------------------------------------------
Dim sh As Worksheet
Set sh = Sheets("Izdato")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
'code for header___________________________________________________________
Me.ListBox1.AddItem "ID"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "Broj kutije"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Naziv"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Kolicina"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "JM"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "Status"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "Datum vracanja"
Me.ListBox1.List(ListBox1.ListCount - 1, 7) = "Napomena"
'__________________________________________________________________

'search code-------------------------------------------------------
For i = 1 To sh.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 1))
p = Me.TextBox7.TextLength
If LCase(Mid(sh.Cells(i, 1), x, p)) = Me.TextBox7 And Me.TextBox7 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 4)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 5)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 10)
.List(ListBox1.ListCount - 1, 7) = sh.Cells(i, 13)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 9)
End With
End If
Next x
Next i
End Sub

 

[EXCEL MAX]

Hello Mir994stan,
I think you need to add one column to your userform listbox and hide it.
When you filling listbox pull row number as identifier in this hidden column.
It's some kind of the Primary Key, unique identifier for every transaction.
When you calling specific record from worksheet, call it by this hidden PK, not ID.
Pozdrav.

 

[EXCEL MAX]

Also you can use listbox listindex property for this purpose instead new column but calculate the row.

 

[mir994stan]

Also you can use listbox listindex property for this purpose instead new column but calculate the row.

I thought about that, but i will lose synch with my 2nd workbook. All my data are linked with that ID number from A column. Is there any way to get addres of selected record in listbox, for example i sellect record in row 40 and value is in 4th column (D), so (40,4)? And based on that data i edit amount in that record?

 

[mir994stan]

I added one more column for row number , it tells me in which row searched ID is found. But how can i now select specific row and make change in column, now when i know row number? Do i need to make new text box that will read value from new row number column in listbox and base on that value make selection of row? Im not this smart, to figure this out :D

 

[EXCEL MAX]

You are close...

Private Sub ListBox1_Click()

Dim vPrimaryKey As Long
vPrimaryKey = ListBox1.Column(0)
TextBox1.Text = Cells(vPrimaryKey, 1)
TextBox2.Text = Cells(vPrimaryKey, 2)
TextBox3.Text = Cells(vPrimaryKey, 3)

End Sub

 

[mir994stan]

You are close...

Private Sub ListBox1_Click()

Dim vPrimaryKey As Long
vPrimaryKey = ListBox1.Column(0)
TextBox1.Text = Cells(vPrimaryKey, 1)
TextBox2.Text = Cells(vPrimaryKey, 2)
TextBox3.Text = Cells(vPrimaryKey, 3)

End Sub

I did it, i made new textbox that display row number, and set macro to be trigered on change and when number is changed it populate all other textboxes with desired values. So i completed this problem as well. Thanks for hints and help. Cheers

 

[EXCEL MAX]

I'm glad if this was helpful.