ActiveX Scrollbar

[Greenbriars]

I am pretty sure that this is trivial, but I have been unable to find a solution online. The scenario is as follows:

My daughter has just opened her first bank account and I am trying to get her interested in managing her money. I have created a spreadsheet which will record her transactions, graph her expenditure and reconcile her bank statement.

I want to be able to show her transactions on the main tab of the worksheet. As the transactions grow, I want to be able to use a scrollbar to control which transactions I can see.

I can achieve this with a Form Control, but I need to manually adjust the max limit depending on the number of transactions. I want to use an ActiveX scrollbar to do this dynamically.

I have attached an excerpt from the sheet. What I want is to know what code to use to control the scrollbar in tab "Report". I need to reference the linked cell to B2 in "Staging" and the Max value to cell B6 also in "Staging".

Thanks for your help in advance.

Greenbriars

 

[Deepak]

Are u looking for this!!!

 

[Greenbriars]

Are u looking for this!!!

Thanks very much for your help. This is just what I had in mind.

I have one small question. When I use the scrollbar the table scrolls perfectly, but the scroll button flashes when you release the mouse button. Is this normal?

Thanks again.

Greenbriars

 

[Deepak]

Ya! It's naturally by design. It only blinks when it has the focus.

Alternate.....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Shapes("ScrollBar1").OLEFormat.Object.Object.Max = Sheet3.[B6].Value
    ActiveSheet.Shapes("ScrollBar1").OLEFormat.Object.Object.LargeChange = Sheet3.[B6].Value + 1
End Sub