If you have too large table in sheet, in such a table ,It is difficult to examine the table and to distinguish the results .
We can create a scrolling table using scrollbar control to overcome this problem. This is a great way to allow more data in a small space. When a user changes the scrollbar, the data accordingly changes.
- Before ,a scrollbar is added to the worksheet. A scrollbar to add to the sheet :
Go to Developer Tab –> Insert –> Scroll Bar (Form Control).
Click on Scroll Bar (Form Control) button and click anywhere on your worksheet.
Right click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.
In Format Control dialogue box go to ‘Control’ tab, and make the following changes:
Current Value: 1
Minimum Value: 1
· Maximum Value: (
Incremental Change: 1
Page Change: 10
Cell Link: $K$2
- Column headings are entered with formulas starting from cell B2 (=Data!A1,=Data!B1)
- The following formula is entered in the first cell (B3) and copied it to fill all the other cells:
=OFFSET(Data!A2;$K$2;0;1;1)
OFFSET formula is dependent on cell K2.
- Following Formula is entered to cell K4 :
=COUNTA(Data!$A:$A)-1
- Lastly following codes are entered to worksheet module in VBA Window to create dynamic scrollbar (for scrollbar max value) :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("K6")
ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Max = Target.Value
End Sub
