List Box Highlight Selection from Sheet

[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

 

[Nigel]

Unfortunately you cannot change the font or color selectively in a list box.

 

[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

 

[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

 

[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

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