Wrong Row Selected

[Sue]

Hi

I am using the code below to highlight a row in a listbox - however it
always selects a row 8 rows below which it should select. The start row for
Column "C" is row 9 could this be the reason?? have tried all different bits
of code all to no avail can anybody help me.


Private Sub Add602_Click()


Dim rngToSearch As Range
Dim rngFound As Range
Startrownumber = 9
Set rngToSearch = ActiveSheet.Columns("C")
Set rngFound = rngToSearch.Find(What:=Tb1B.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb1B.Value & " was not found."
Else
LisBox1.ListIndex = rngFound.Row - 2
End If
End Sub

 

[Sue]

Hi

I eventually Worked it out instead of -2 changed it to -9

 

[Patrick Molloy]

so
the list data starts at row 9
remember the 1st item in a listbox is undex zero as its zero based

thus
LisBox1.ListIndex = rngFound.Row - 2

in your code you define Startrownumber but don't use it...

so the line should be
LisBox1.ListIndex = rngFound.Row - Startrownumber

 

[Sue]

Hi

I have done as you suggested many thanks for that -- what is the best way to
get info out of the List Box -- say there are 5 columns and I want to put
each row when selected into 5 separate text boxes
--
Many Thanks

Sue

so
the list data starts at row 9
remember the 1st item in a listbox is undex zero as its zero based

thus
LisBox1.ListIndex = rngFound.Row - 2

in your code you define Startrownumber but don't use it...

so the line should be
LisBox1.ListIndex = rngFound.Row - Startrownumber

 

[Patrick Molloy]

if each item in the list if 5 values, comma separated, eg "A,B,B,D,E"
then assuming you have 5 text boxes "textbox1,textbox2....textbox5
you could use the SPLIT function

Private Sub ListBox1_Click()
Dim data As Variant
Dim index
If ListBox1.ListIndex = -1 Then Exit Sub
data = Split(ListBox1.Value, ",")
For index = 1 To 5
Controls("Textbox" & index) = data(index - 1)
Next
End Sub

OR if your listbox has 5 columns ...

Private Sub ListBox1_Click()
Dim index As Long
If ListBox1.ListIndex = -1 Then Exit Sub
With ListBox1
For index = 1 To 5
Controls("Textbox" & index 1) = .List(.ListIndex, index-1)
Next
End With
End Sub

Hi

I have done as you suggested many thanks for that -- what is the best way
to
get info out of the List Box -- say there are 5 columns and I want to put
each row when selected into 5 separate text boxes

 

[Rick Rothstein]

Assuming your ListBox Column property is set to 5, and that your TextBox'es
are named TextBox1, TextBox2, etc. (the key here is that the TextBox names
have the same textual base and have sequential numbers affixed to the end of
that textual base), this code fragment will assign each of the TextBox's
Column values (for the row that was clicked on) to those TextBox'es...

Dim X As Long
......
......
For X = 1 To 5
Me.Controls("TextBox" & X).Text = Add602.Column(X - 1, Add602.ListIndex)
Next

You should be able to integrate this into your own existing code.

--
Rick (MVP - Excel)

Hi

I have done as you suggested many thanks for that -- what is the best way
to
get info out of the List Box -- say there are 5 columns and I want to put
each row when selected into 5 separate text boxes

 

[Sue]

Hi

Sorry guys cannot get any of your sugestions working just keep getting
error messages.

 

[Rick Rothstein]

1. Where is the ListBox located at... on the worksheet or on a UserForm?

2. If on the worksheet, which toolbar did you get it from... the Forms
Toolbar or the Control Toolbox Toolbar?

3. What is the name of your ListBox and TextBox'es?

4. And most importantly, what does the error message you get (when you run
my code) say?

 

[Sue]

1. Where is the ListBox located at... on the worksheet or on a UserForm?

2. If on the worksheet, which toolbar did you get it from... the Forms
Toolbar or the Control Toolbox Toolbar?

3. What is the name of your ListBox and TextBox'es?

4. And most importantly, what does the error message you get (when you run
my code) say?

 

[Rick Rothstein]

I don't see any new text for me to read in your posting.

 

[Sue]

Hi

Keep getting service not available please try later that is on the MS
Discussion group site so I'm going to leave it until tomorrow.

Hopefully we can catch up then

Many Thanks

Sue

 

[Sue]

Hi Mr Rothstein
Eventually when I had slept on the problem I solved it all with the following

Private Sub Add602_Click()


Dim rngToSearch As Range
Dim rngFound As Range
Dim X As Long
Set rngToSearch = ActiveSheet.Columns("C")
Set rngFound = rngToSearch.Find(What:=Tb1B.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb1B.Value & " was not found."
Else
LisBox1.ListIndex = rngFound.Row - 9
With LisBox1

Tb71.Text = .List(.ListIndex, 1)
Tb71Z.Text = .List(.ListIndex, 0)
End With
End If
End Sub

and it appears to work OK