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 :
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:
· Maximum Value: (It will be created with codes in worksheet module)
OFFSET formula is dependent on cell K2.
- Following Formula is entered to cell K4 :
- 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)