"scroll to" by list box selection

[Sam]

Need some help please.....

I have a listbox(drop down list) on a custom command bar - the drop
down list has the names of all the seperate catagories in my
spreadsheet.

This is the macro that currently runs from the "OnAction" of the above
commandbar listbox.
When an list item (catagory name) is clicked a message box appears
with a message corresponding to the catagory clicked.

Sub Catagory_Select()
Dim cbcCommandBarListBox As CommandBarControl
Set cbcCommandBarListBox = CommandBars("Product Sales Form "). _
FindControl(Tag:="Catagory")

If Not cbcCommandBarListBox Is Nothing Then
MsgBox "You Selected " & cbcCommandBarListBox. _
List(cbcCommandBarListBox.ListIndex)

****************************************************
How can I replace the messagebox that appears with an action that
scrolls the spreadsheet to the selected catagories row ?

For reference: The catagory rows are listed below in the code
from an old command button.


Case "Category Name 1"
Range("F53").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name2 "
Range("F83").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name3 "
Range("F160").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name4 "
Range("F353").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name 5"
Range("F436").Select
ActiveWindow.ScrollRow = ActiveCell.Row

***************************************
I tried replacing the above "If statement" with:

If Not cbcCommandBarListBox Is Nothing Then
Select Case cbcCommandBarListBox.Value

and also

If Not cbcCommandBarListBox Is Nothing Then
Select Case cbcCommandBarListBox.text

neither work

what am I leaving out or doing wrong
any help appreciated.
Thank you
Sam

 

[Tom Ogilvy]

Sub Catagory_Select()
Dim cbcCommandBarListBox As CommandBarControl
Set cbcCommandBarListBox = CommandBars("Product Sales Form "). _
FindControl(Tag:="Catagory")

Select Case cbcCommandBarListBox. _
List(cbcCommandBarListBox.ListIndex)

Case "Category Name 1"
Range("F53").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name2 "
Range("F83").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name3 "
Range("F160").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name4 "
Range("F353").Select
ActiveWindow.ScrollRow = ActiveCell.Row
Case " Category Name 5"
Range("F436").Select
ActiveWindow.ScrollRow = ActiveCell.Row
CaseElse
'do nothing
End Select

End Sub

--
Regards,
Tom Ogilvy



***************************************
I tried replacing the above "If statement" with:

If Not cbcCommandBarListBox Is Nothing Then
Select Case cbcCommandBarListBox.Value

and also

If Not cbcCommandBarListBox Is Nothing Then
Select Case cbcCommandBarListBox.text