Trick to make a dynamic table in MS Excel

In this article we will go through adding a dynamic grid view type table structure with scroll bar control

Sometimes we might come across a situation where we have to display an entire dataset by using a scroll bar control. Take for example if we have to display student records for a given class consisting of more than 100 students and also you just have a display window of 20 rows at a time.

The simplest options to do this would be create a table like structure by using cell formatting and then populate the inner cells by 20 rows of data. The data population would be triggered by the changing of the scroll bar control.

Since there is no grid view control in MS Excel and we are just dealing with data filled worksheets instead of databases therefore this could be used as an easy and simple trick.

The task can be done by following the given easy to understand steps.

Step 1:

Arrange all your data into a sheet in the same table structure as you would want to display in the table.

I have used a dataset of 151 rows, consisting of demo student name, roll, etc in a single datasheet.

Step 2:

In another datasheet, use cell formatting with background colors to prepare a table like structure.

Keep exact number of blank rows for data you would like to show.

Step 3:

Go to developer tab and add a scroll bar ActiveX Control and set its properties, i.e., min max values.

Set the min value as the row number of the 1^st row of your dataset. The max value should be last row number of your dataset minus the number of blank rows in your table structure.

The max value is this because we would want the table to display till the last row when the scroll bar is at the bottom most position.

Every instance of scroll bar change results in displaying 20 rows (number of blank rows)

Step 4:

Open the VBA window and in the change event of the activex scroll bar control write the following lines of code.

• selectColRange = "A:D"
• Set copyRange = data.Range(selectColRange)
• copyRange.Range("A" + CStr(ScrollBar1.Value) + ":D" + CStr(ScrollBar1.Value + 20)).Copy ThisWorkbook.Sheets("Summary").Range("C7")

The 1^st line is used for declaring the column range which you want to display

The 2^nd line is used for setting the copy range. Here data is the name of the sheet where we have our dataset. If you didn’t set the data sheet name then data object name can be replaced with Sheets(“<Sheet Name>”).

The 3^rd line is used for setting the copy range in the dataset.

• ("A" + CStr(ScrollBar1.Value) gives the 1^st cell coordinate of the copy range.
• D" + CStr(ScrollBar1.Value + 20) gives the last cell coordinate of the copy range.
• ScrollBar1.Value + 20 is used because we want a set of 20 rows to be displayed in the table structure.
• ThisWorkbook.Sheets("Summary").Range("C7"): The table structure is on Summary sheet. And Range C7 is the cell coordinate where the copy range block will be copied to.

Step 5:

Save the file as required and then click scroll bar to see the effect.

Example File link:
https://drive.google.com/file/d/0B20WcznMWRTfcVRWTjJKODRZbFk/view?usp=sharing