[SOLVED] Excel Scroll Bar User Form won't Continuously Update when Dragged

Excel Scroll Bar User Form won't Continuously Update when Dragged

I am designing a spreadsheet that will utilise a user form with a scroll bar on it. I need to scroll bar to update the cell specified in real time as I drag the bar using the mouse, at present it only adjusts the value In the cell when I release the mouse.

I initially wanted to use a slider but from research it appears that these can't be used in a userform.

Does anyone know any VBA code or anything that will make the scroll bar continuously update as I drag the bar using the mouse?

Solution

If you are using the scroll bar on a userform and you want the update to be while dragging you need your code to be in the scroll bar's scroll event handler. The behavior that you descried happens with the scroll bar's change event handler. To see the difference, create a new userform with a scroll bar as the only control on it. In it's code module enter the following two event-handlers. When you run it you should see B1 but not A1 updating smoothly:

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

Private Sub ScrollBar1_Scroll()
    Range("B1").Value = ScrollBar1.Value
End Sub