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

mir994stan

New Member
Joined
Jul 18, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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 Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
Also you can use listbox listindex property for this purpose instead new column but calculate the row.
 

mir994stan

New Member
Joined
Jul 18, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 18, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 11, 2020
Messages
499
Office Version
  1. 2016
Platform
  1. Windows
You are close...
VBA Code:
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
 
Solution

mir994stan

New Member
Joined
Jul 18, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
You are close...
VBA Code:
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
 

Forum statistics

Threads
1,144,237
Messages
5,723,182
Members
422,482
Latest member
MacSapper

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