Prevent scrollbar control from changing current selection

[holmes123]

I use an ActiveX Scroll Bar control to allow a user to scroll among 10 rows that show fruits, with 5 rows/fruits showing at a time.


When he selects a fruit by clicking in the range, rngFruits, a WorkSheetChange event assigns the row number for the selected fruit to valFruit_Row:


[valFruit_Row] = ActiveCell.Row() - TopRow + 1


Then, valFruit_Name, returns the name of the fruit:


=INDEX(rngFruits,valFruit_Row)


So if "Orange," which is on row #3 is selected, I have:


valFruit_Row = 3

valFruit_Name = Orange


When the user uses the scroll bar, valFruit_Row stays the same (as it should) but valFruit_Name changes (as it should). But is there anyway to prevent valFruit_Name from changing? Several displays are based on the currently selected Fruit name, so I'd like it to remain unchanged when the user scrolls among the fruits. Otherwise, the displays update with every click of the scrollbar.

 

[Luke M]

Instead of having the scroll bar use a linked cell, you could use the lose focus event. Something like

[pre]

Private Sub ScrollBar1_LostFocus()
Range("A1").Value = ScrollBar1.Value
End Sub

[/pre]
A1 in this case would not get changed until user clicks somewhere other than on the scroll bar.

 

[holmes123]

> A1 in this case would not get changed until user clicks somewhere other than on the scroll bar.


I made that change and A1 never changes when the user clicks elsewhere. Nothing happens. I also suspect I left out some important details.


The rows that show fruit details (in rngFruits) are based on this formula:


=INDEX(rngFruits,$A$1+Staging!$H4,1))


Where:


- $A$1 is the linked cell for the scrollbar

- Staging!$H4 is the row number for the first fruit in a list.

(Staging!$H5 is the row number for the second fruit in a list, etc.)


The scroll bar also has a macro (Call AdjustMax) assigned to it's Change, GotFocus, and Scroll events.


Sub AdjustMax()

ActiveSheet.ScrollBar1.Max = Range("valMaxFruitRow").Value

End Sub


This changes the max value for the school bar so it's equal to the maximum number of fruits on the Staging! sheet, minus five. (Sometimes there are 10 fruits, sometimes 15, etc.) This stops the scroll bar from scrolling when there are no more fruits (beyond the 5 being displayed) to show.