Wrong Row Selected
 

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

--
Many Thanks

Sue

Wrong Row Selected
 

Hi

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

Sue


"Sue" wrote:

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

--
Many Thanks

Sue

Wrong Row Selected
 

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

Hi

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

Sue


"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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 (MVP - Excel)


"Sue" wrote in message
...
Hi

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

Sue


"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

"Rick Rothstein" wrote:

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 (MVP - Excel)


"Sue" wrote in message
...
Hi

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

Sue


"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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

--
Rick (MVP - Excel)


"Sue" wrote in message
...


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Sue" wrote in message
...
Hi

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

Sue


"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Sue" wrote in message
...


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Sue" wrote in message
...
Hi

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

Sue


"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue

Wrong Row Selected
 

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


--
Many Thanks

Sue


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Sue" wrote in message
...


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Sue" wrote in message
...
Hi

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

Sue


"Rick Rothstein" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

--
Many Thanks

Sue