Dynamic table with vertical and horizontal scrolling options in Excel

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

Sometimes we might come across a situation where we have to display an entire dataset by using both horizontal and vertical 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 and 4 columns 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.

Step 1:

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

We have used a dataset of 151 rows, consisting of demo student details spread across 11 columns in a single datasheet.

Step 2:

In another worksheet, 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 vertical and horizontal scroll bar ActiveX Control. Set the min, max and value properties of the scroll bar controls while workbook_open event triggers

tool is the object name of the worksheet that contains the table structure. data is the object name of the worksheet that contains the data structure. usedrange gets the number of cells that contains data. sb_horizontal is the object name of the horizontal scroll bar control. sb_vertical is the object name of the vertical scroll bar control.

For the vertical scroll bar control; set the min value as the row number of the 1st row of your dataset. The max value should be last row number of your dataset minus the number of rows in the table structure.

For the horizontal scroll bar control; set the min value as the column number of the 1st column of your dataset. The max value should be last column number of your dataset minus the number of columns in the table structure.

The max value is this because we would want the table to display till the last row and column when the scroll bars are at their bottom most position.

Every instance of scroll bar change results in displaying 20 rows and 4 columns (number of blank rows and columns in the table structure)

Step 4:

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

For the horizontal scroll bar control

1. Range H28 and K28 will contain the column reference of the first and last column position set through the horizontal scroll bar control.
2. Then the heading of the dataset is copied from the data sheet and pasted in the set range H4:K4
3. And the vertical scroll bar’s change event is called to refresh the data structure.

For the vertical scroll bar control

1. CStr(Mid(tool.Range("H28").Value, 2, 1) takes the column reference value from the H28 cell
2. CStr(Mid(tool.Range("K28").Value, 2, 1) takes the column reference value from the K28 cell
3. CStr(sb_vertical.Value + 20) for selecting plus 20 rows from the initial row number
4. Range H5:K25 is the range which is used as the reference to where the data is to be pasted

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/0B20WcznMWRTfV3p5bGx1Q3dsRk0/view?usp=sharing

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