Identify row number of the data in a listbox

[dss28]

I have a listbox which is filled with data transferred from textboxes in the userform and not from sheet. The listbox has multiple rows and columns.
I have also added a code to delete a row before final saving of the data to excel sheet.

Hence if I try to give / add a row number through a textbox in the userform to rows, it might get altered if the user deletes any of the rows before final saving the data to sheet.

e.g. if initially I transferred data in 5 rows to the listbox1, with Sr. No. 1 to 5 assigned to them through a textbox, but then decide to delete 3rd and 4th row so finally it will have only three rows numbered 1st, 2nd and 5th (now 3rd after deletion of two row) to save to the sheet. Here the sequence of row number is not proper. I want to save them as Sr. No. 1, 2, 3

My idea is to assign row number by any code to these three final rows so that the same can be captured in one of the columns (say ColumnH) in the sheet. is it possible?
Can anyone help..

 

[Norie]

Why do you need to assign a row number when adding data to the listbox?

 

[dss28]

Thanks Norie for the interest in my post.
I want to transfer the data from listbox to a sheet and identify each row entry with a serial number 1,2,3 which is captured in column M in this case. The multiple lines I save to the sheet from the listbox at a time belong to a particular set of case and hence serial numbering each row.
The data in sheet will look like: column M is sr. no.

columnA column M
row 1 ----- case 1 1 and other columns
row2 ------case 1 2 and other columns
row3 ------case 2 1 and other columns
row4 ------case 2 2 and other columns
row5 ------case 2 3 and other columns
row6 ------case 2 4 and other columns

I can assign row number .. through a text box which I input 1,2,3 for each row data being transferred to listbox but since in this case i have the option of deleting a row in the list box before transferring to sheet, the row number assigned will not be consecutive if some rows are deleted in between.
hence for the sake of identifying the data in sheet for each row, i wanted to assign a serial number to the rows now present in the listbox during transfer of the data through some coding.
hope I am able to explain properly what I need.

 

[Norie]

Can't you determine the serial no when you are transferring the data to the sheet?

i.e. get the last serial no in column M and as you add each row of data from the listbox increment that and set it as the serial no for the new row.

 

[dss28]

No, since each set of data, I transfer at a time belongs to one case which has serial No. starting from 1 each time.
Second case will have multiple rows with serial no. starting from 1 again.

 

[Norie]

That's even easier, set the serial no to 1 for the first row and increment it by 1 as you add the other rows.

How are you transferring the data from the listbox to the sheet?

 

[dss28]

That's even easier, set the serial no to 1 for the first row and increment it by 1 as you add the other rows.

How are you transferring the data from the listbox to the sheet?

Yes i can do that through a textbox while transferring the data . but my problem is if a middle row is deleted in the listbox, the row numberng will get disturbed.
is there any other method to number the row in listbox?

i am trying the following code for transferring data from listbox to sheet. however struggling to set the column A as irow value (Code betweem the two line "++++ is not final) but the rest code works. Will glad to receive help in this matter to if possible.

Private Sub CommandButton4_Click()   ' multi row data save from listbox to sheet

Dim i As Long
Dim ws As Worksheet
Dim eR As Long
Set ws = Sheet10

‘++++++++++++++++++++++++++++++++++++++
Dim iRow As Long

    If UserForm3.TextBox22.Value = "" Then    ' hidden textbox

        iRow = Sheet10.Range("A" & Rows.Count).End(xlUp).row + 1 

    Else

        iRow = UserForm3.TextBox22.Value

    End If

    With Sheet10.Range("A" & iRow)

        .Offset(0, 0).Value = "=Row()-1"
‘++++++++++++++++++++++++++++++++++++++

    For i = 0 To ListBox1.ListCount - 1
    eR = WorksheetFunction.CountA(ws.Range("B:B")) + 1
  
        ws.Cells(eR, 1).Value = iRow + 1 ' irow no.

        ws.Cells(eR, 2).Value = ListBox1.Column(7, i)   
        ws.Cells(eR, 3).Value = ListBox1.Column(8, i) 
        ws.Cells(eR, 4).Value = ListBox1.Column(9, i)   
        ws.Cells(eR, 5).Value = ListBox1.Column(10, i)   
        ws.Cells(eR, 6).Value = ListBox1.Column(11, i)   
        ws.Cells(eR, 7).Value = ListBox1.Column(12, i)   
        ws.Cells(eR, 8).Value = ListBox1.Column(0, i)   
        ws.Cells(eR, 9).Value = ListBox1.Column(1, i)   
        ws.Cells(eR, 10).Value = ListBox1.Column(2, i)   
        ws.Cells(eR, 11).Value = ListBox1.Column(3, i)   
        ws.Cells(eR, 12).Value = ListBox1.Column(4, i)   
        ws.Cells(eR, 13).Value = ListBox1.Column(5, i)   
        ws.Cells(eR, 14).Value = ListBox1.Column(6, i)   
        ws.Cells(eR, 15).Value = ListBox1.Column(13, i)   
        ws.Cells(eR, 16).Value = ListBox1.Column(14, i)   
        ws.Cells(eR, 17).Value = ListBox1.Column(15, i)   
        ws.Cells(eR, 18).Value = Application.Username   
        ws.Cells(eR, 19).Value = Now() 
        
        
    Next i
    

    Call CommandButton6_Click ‘ to refresh the form for fresh data input

End With

End Sub

 

[Norie]

Don't add the serial no to the listbox, add it when you are transferring the data to the sheet.

 

[abdelfattah]

@dss28
hi
just guess . not guaranteed if this works for you
try this
option1
delete this

        .Offset(0, 0).Value = "=Row()-1"

 With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
           .Formula = "=ROW()-1"
           .Value = .Value
           End With

option2

            listbox1.List(lindex, 0) = lindex + 1

I hope this help

 

[dss28]

Don't add the serial no to the listbox, add it when you are transferring the data to the sheet.

can you please suggest. also can you help me to assign code to column A to give irow values.