There may be a requirement to view data on excel VBA form, in case you are building an Excel VBA application.
This data might be used to view a list of points to cross audit or adding a check if your data is updated correctly and many more audit options.
In order to create a sample form, we need prepare a data input. You can copy paste the below table to your excel sheet and follow the steps, below.
1. Open excel workbook and copy the below table to sheet1
2. As per above table our data is in Sheet1, within range from A1 to F10.
3. Press Alt + F11 and open the VBA editor window
4. Right click on Microsoft Excel Objects and Click on Insert --> Module. For more details check out my post on How to use Visual Basic Editor in Excel VBA ?
5. Right click on Microsoft Excel Objects and Click on Insert --> UserForm
6. A new UserForm will be inserted, adjust the length of the UserForm as required to view the 6 columns.
7. Now add a list box from the VBA Editor Tool Box window. (If you are not able to find the tool box on screen, then Go to View menu on the top-->Click on Toolbox). Adjust the size of the list box to fit the 6 columns of the data table.
9. Double click on CommandButton1 and this will take us to the vba editor screen of the form. We will be able to see the below code:
Private Sub CommandButton1_Click()
End Sub
10. Copy paste the below code in the CommandButton1 click event.
--------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
With ListBox1
.ColumnCount = 6
.ColumnWidths = "50;70;90;70;70;50"
.RowSource = Sheet1.Range("A1:F20").Address
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------
11. How this works ?
--------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
' below code will work on the Listbox1, which we have added on the form
With ListBox1
' We can change the number of the columns we need to view in the list box. In this case we need to view 6 columns.
.ColumnCount = 6
'Below code defines what will be the column width. We can adjust this column width based on the data requirement.
.ColumnWidths = "50;70;90;70;70;50"
'In below line we are providing our table data range, which needs to be populated in the Listbox1
.RowSource = Sheet1.Range("A1:F20").Address
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------
12. Once the code is added, Click on Run button on top menu or press F5 on keyboard.
13. We will be able to see the below form. Here click on command button.
14. Once we click on command button, we will be able to see the below result.
15. The list box here, is used to populate the table data in tabular format. We provided the # of columns, column width and the data range source to the list box. The list box provide an horizontal and vertical scrollbars, based on the data range.
Please feel free to customize this code as per the requirements.
Also check out my posts on :
This data might be used to view a list of points to cross audit or adding a check if your data is updated correctly and many more audit options.
In order to create a sample form, we need prepare a data input. You can copy paste the below table to your excel sheet and follow the steps, below.
1. Open excel workbook and copy the below table to sheet1
| User Name | Communication | Leave Management | Training Score | Productivity | Quality |
| ABC | 10 | 3 | 94 | 56 | 98 |
| DEF | 10 | 4 | 42 | 49 | 91 |
| GHI | 8 | 4 | 100 | 60 | 91 |
| JKL | 3 | 9 | 40 | 60 | 90 |
| MNO | 1 | 3 | 52 | 62 | 95 |
| PQR | 8 | 5 | 74 | 45 | 85 |
| STU | 10 | 6 | 74 | 65 | 96 |
| VWX | 10 | 3 | 51 | 94 | 95 |
| YZA | 9 | 8 | 72 | 61 | 93 |
2. As per above table our data is in Sheet1, within range from A1 to F10.
3. Press Alt + F11 and open the VBA editor window
4. Right click on Microsoft Excel Objects and Click on Insert --> Module. For more details check out my post on How to use Visual Basic Editor in Excel VBA ?
5. Right click on Microsoft Excel Objects and Click on Insert --> UserForm
6. A new UserForm will be inserted, adjust the length of the UserForm as required to view the 6 columns.
7. Now add a list box from the VBA Editor Tool Box window. (If you are not able to find the tool box on screen, then Go to View menu on the top-->Click on Toolbox). Adjust the size of the list box to fit the 6 columns of the data table.
8. Add a Command Button on the form we have created. The code to view the data in list box, will go into this command button.
9. Double click on CommandButton1 and this will take us to the vba editor screen of the form. We will be able to see the below code:
Private Sub CommandButton1_Click()
End Sub
--------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
With ListBox1
.ColumnCount = 6
.ColumnWidths = "50;70;90;70;70;50"
.RowSource = Sheet1.Range("A1:F20").Address
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------
11. How this works ?
--------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
' below code will work on the Listbox1, which we have added on the form
With ListBox1
' We can change the number of the columns we need to view in the list box. In this case we need to view 6 columns.
.ColumnCount = 6
'Below code defines what will be the column width. We can adjust this column width based on the data requirement.
.ColumnWidths = "50;70;90;70;70;50"
'In below line we are providing our table data range, which needs to be populated in the Listbox1
.RowSource = Sheet1.Range("A1:F20").Address
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------
12. Once the code is added, Click on Run button on top menu or press F5 on keyboard.
13. We will be able to see the below form. Here click on command button.
14. Once we click on command button, we will be able to see the below result.
15. The list box here, is used to populate the table data in tabular format. We provided the # of columns, column width and the data range source to the list box. The list box provide an horizontal and vertical scrollbars, based on the data range.
Please feel free to customize this code as per the requirements.
Also check out my posts on :
- How to write a For Next Loop in Excel VBA ?
- Find first and last date of any month
- Find the difference between the two dates in excel...
- How to use the CASE and the INPUTBOX in Excel VBA ...
- How to calculate days in a month using Excel formu...
- How to create Cell charts in excel ?
- Working with Autofilters in Excel VBA
- Macro Security in Excel VBA
- How to protect vba module in excel ?
- How to split or delimit data using Excel VBA code ...
No comments:
Post a Comment