List Box Highlight Selection from Sheet

S

Sue

Hi Everybody

I have a sheet using Rowsource A2:G470 which populates a Listbox
in Column G is a unique number for each row.

On a Userform I use the following to find the number on the sheet in Column
G when it finds the number is it possible for the row in the list box to be
highlighted.

below is the code I'm using to find the number on the sheet.

Private Sub Cmd100_Click()
Dim rngToSearch As Range
Dim rngFound As Range

Set rngToSearch = ActiveSheet.Columns("G")
Set rngFound = rngToSearch.Find(What:=TB100.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TB100.Value & " was not found."
Else

rngFound.Select
ActiveCell.Interior.Color = RGB(126, 255, 126) 'Green
End If


End Sub

Many thanks
 
J

Jim Cone

Sue,
Well, you could change the found text to all upper case.
However, that means you would then need a loop to change the
worksheet list to lower case before each search.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Sue"
wrote in message
Hi Everybody
I have a sheet using Rowsource A2:G470 which populates a Listbox
in Column G is a unique number for each row.

On a Userform I use the following to find the number on the sheet in Column
G when it finds the number is it possible for the row in the list box to be
highlighted.

below is the code I'm using to find the number on the sheet.

Private Sub Cmd100_Click()
Dim rngToSearch As Range
Dim rngFound As Range

Set rngToSearch = ActiveSheet.Columns("G")
Set rngFound = rngToSearch.Find(What:=TB100.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TB100.Value & " was not found."
Else

rngFound.Select
ActiveCell.Interior.Color = RGB(126, 255, 126) 'Green
End If
End Sub

Many thanks--
Many Thanks
Sue
 
R

Rick Rothstein \(MVP - VB\)

What about this code (all I changed is your Else code block)...

Private Sub Cmd100_Click()
Dim rngToSearch As Range
Dim rngFound As Range

Set rngToSearch = ActiveSheet.Columns("G")
Set rngFound = rngToSearch.Find(What:=TB100.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TB100.Value & " was not found."
Else
ListBox1.ListIndex = rngFound.Row - 2
End If
End Sub

What it should do is highlight the row in your ListBox (assumed to be named
ListBox1 for the example code; change the name to your actual ListBox name).

Rick
 
S

Sue

Hi all you Guys

I probably mislead you with my question -- I didn't want the List Box to
change colour just wanted to find the row in the List Box when the Unique
Number was found - which is what happened when I used Rick's code worked
exactly as I wanted -- thank you all for your help. Have to be more specific
next time I ask a question.
--
Many Thanks

Sue


Rick Rothstein (MVP - VB) said:
What about this code (all I changed is your Else code block)...

Private Sub Cmd100_Click()
Dim rngToSearch As Range
Dim rngFound As Range

Set rngToSearch = ActiveSheet.Columns("G")
Set rngFound = rngToSearch.Find(What:=TB100.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TB100.Value & " was not found."
Else
ListBox1.ListIndex = rngFound.Row - 2
End If
End Sub

What it should do is highlight the row in your ListBox (assumed to be named
ListBox1 for the example code; change the name to your actual ListBox name).

Rick
 

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