Scrollbar "GoTo" Formatting/Code

[Steve Morris]

I am new to using the combo box and scrollbar. The combo box was easy enough but I can't figure out how to center align the content.

The scrollbar is formatted to highlight the city selected from the combo box. It would be nice to take it a step further and move the section to the city and data even if it isn't in the view of the current scroll table. Additionally, I have set it up with a max value of 66 which is the number of cities but the scroll continues past that.

Help:

• center align combo box content
• move scroll data to show the city once it is selected in the combo box
• limit the scroll data to the 66 cities

 

[NARAYANK991]

Hi Steve ,

See your file now.

Narayan

 

[Steve Morris]

Thanks...that is exactly what I need. I see the match formula but assume there is some code working in the background. I have no VBA experience so not sure what detail I am missing. Additionally, I see the number has been changed on the scrollbar from 66 to 53. How is the number determined from a list of 66 to only show those items in it.

 

[NARAYANK991]

Hi Steve ,

The reason is that we do not want the last item ( Winston Salem ) to appear in the top-most row , since then all the remaining 12 rows ( there are 13 rows in all in the scroll area ) will show 0 , due to the formula.

So any time the scroll bar value goes above 53 , we do not do anything ; effectively we clamp the maximum value of the scroll bar to 53.

The code itself is not too complicated ; all it does is connect the scroll bar value to the value returned by the MATCH function is D1 , so that the combo box and the scroll bar are connected together.

You might have noticed that I have added a Worksheet_SelectionChange event procedure , so that moving the cursor in the scroll area changes the combo box value , so that this is another way of changing the chart selection.

Narayan