Problem getting correct Listbox selected item row

[sharky12345]

I'm using this to identify the row number of a Listbox selected item;

'All Records
If ComboBox1.Value = "All Records" Then
CmdView.Enabled = True
Total = Sheet9.Range("Q1").Value
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
TextBox4.Value = .List(i, 14)
Set FindRow = Sheet9.Range("O2:O500").Find(What:=TextBox4.Value, LookIn:=xlValues)
TextRow.Value = FindRow.Row
TextBox1.Value = .List(i, 0)
TextBox2.Value = Format(.List(i, 2), "dd mmmm yyyy")
TextBox3.Value = .List(i, 13)
CommandButton4.Enabled = True
End If
Next i
End With
End If

The first row is a header, hence searching for the value from row 2 onwards. In this particular test I have 2 rows of data, each having the value required so would expect to find 2 entries, (which I do), and in theory they should be identified as being on row 2 and 3 respectively.

The issue I'm having is that the very first item in the Listbox always produces a row number of 3, when in this example it should be 2 then 3.

Can anyone show me where this is going wrong please?

 

[mole999]

i can see you are using .listcount - 1, but where is .listcount being created and what is the original values, possibly something there

 

[sharky12345]

Hi,

Not sure I understand the question - but the code I posted is being triggered on the Listbox.Click event - do you need to know how the Listbox is loaded? If so here;

Dim LastRow As Long

LastRow = Range("D65536").End(xlUp).Row '+ 10
'All
If ComboBox1.Value = "All Records" Then
CmdEdit.Enabled = False
CmdView.Enabled = False
Dim rng As Range, Dn As Range, Ac As Long, c As Long
Label15.Visible = True
Label16.Visible = True
Label16.Caption = "121 Date"
Label17.Visible = True
Application.ScreenUpdating = False
Sheet9.Activate
With Sheet9
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next i