Wrong Row Selected

S

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
 
P

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
 
S

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


Patrick Molloy said:
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
 
P

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


Sue said:
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
 
R

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)


Sue said:
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
 
S

Sue

Hi

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

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?
 
S

Sue

Rick Rothstein said:
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?
 
S

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top