Custom Scrollbar in Excel

[JimLes]

Hello,

I have a scroll bar set up in excel with a linked cell to pull its value. Is there a way to have this scroll bar move through data in a column so I can pull its value as I scroll?

I am using an advance filter to continually refresh the list in this column and I want the scroll bar to retrieve it value as I toggle.

Is this possible? OR I am open to another solution to get the value.

Thanks,

 

[GlennUK]

What are you going to be doing with the value exactly? Are you Advanced Filtering In-Place, or Copying To Another Location? If you are Copying To Another Location then a simple INDEX into the results will work.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[JimLes]

I first advance filter a list through VBA to a location in my spreadsheet. The list only pulls the auto number from each manager in the dataset that matches the criteria.

I am then trying to use the scrollbar to cycle through the new list which is not more than 20 lines long. As I cycle through the list I want to pull the auto number value to a location which uses a VBA lookup to color code a map of the united states for which are the manager is responsible.

Here is my code below that colors each state based on the value in cell (27,13).

I am totally open to a new way of doing this though.

Thanks,

Private Sub ScrollBar1_Change()
Application.ScreenUpdating = False
On Error Resume Next





ActiveSheet.Shapes(i1).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

ActiveSheet.Shapes(i2).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

ActiveSheet.Shapes(i3).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

ActiveSheet.Shapes(i4).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

ActiveSheet.Shapes(i5).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52


On Error Resume Next
i1 = Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 6, False)
ActiveSheet.Shapes(Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 6, False)).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

i2 = Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 7, False)
ActiveSheet.Shapes(Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 7, False)).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

i3 = Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 8, False)
ActiveSheet.Shapes(Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 8, False)).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

i4 = Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 9, False)
ActiveSheet.Shapes(Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 9, False)).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

i5 = Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 10, False)
ActiveSheet.Shapes(Application.WorksheetFunction.VLookup(Cells(27, 13).Value, Sheet2.Range("DataSet"), 10, False)).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45


Application.ScreenUpdating = True
Cells(27, 13).Select

End Sub

 

[SkipVought]

Jim,

This discussion would be much better served in the proper forum: Forum707, as this forum, forum68, is suited for native application features, not VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}