Creating A Scrollable List In Worksheet

Excel Create Scrollable List


         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. Scrollbar control 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: (It will be created with codes in worksheet module)
                 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 Report worksheet's Worksheet_SelectionChange method 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

No comments:

Post a Comment