How to view excel data table on VBA Form ?

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

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

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 :

• 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 ...

How to protect vba module in excel ?

If you are building a macro and don't want any one to modify it. You have an option in Excel VBA editor, using which you can protect your VBA module code.

Open a new excel file

Go to Visual Basic Editor Window.

Insert new module and add a sample code to it.

Sub SampleCode()

Msgbox "This is a sample code we need to hide !", vbInformation

End Sub


Follow the below steps to protect the VBA module:

Click on Tools

Click on VBA Project Properties

The VBA object properties window will pop up. On this window, select the Protection Tab.

Click on Lock Project for viewing check box

Enter the password of your choice in two fields, Password and Confirm Password.

Click OK and we have just protected the VBA module. Save and close the workbook. Open it again to check the protection.

Go to VBA module, all modules will be hidden and if you double click on it will ask you the password to unprotect.

Also check out my posts on :

• How to split or delimit data using Excel VBA code ...
• Must know VBA functions in Excel VBA
• What are Objects, Properties and Methods in Excel ...
• How to use comments in the Excel VBA Macro ?
• How to edit and debug the macro created in Excel V...

How to super hide excel sheets using VBA code ?

If you want to super hide the worksheets in excel, excel has an very hidden property. When the sheets are very hidden. When you right click and try to unhide such sheets, the unhide view gets disabled. As there will be no worksheets to view.

Use the below steps to test the very hidden property of the excel sheets.

Open new worksheet

Press Alt + F11 to go to VBA editor window

Insert new module from the project explorer

Copy and paste the below code to try out:

Sub SuperHide()

'This property will super hide the sheets
Sheet1.Visible = xlVeryHidden

End Sub

We will not be able to unhide this sheet1 from excel window. For that we would need to write a vba code for unhide.

Sub UnHide()

'This property will unhide the sheets. True over here reprents the xlSheetVisible property
Sheet1.Visible = True

End Sub

This will unhide the sheet and you will be able to see the same in excel window.

You can add multiple sheets in a similar way and use both the hide and unhide codes for different uses. If you don't want to super hide the sheet, you can also use the below code to just hide the sheet.

Sub Hide()

'This property will simply hide the sheets. False over here represents the xlSheetHidden property
Sheet1.Visible = False 

End Sub

This will hide the sheets and in this case, you will be able to unhide the sheet from the Excel Window.


You can also use the below keyboard shortcuts to hide or unhide sheets for faster use.
Hide - Alt, O, H, H

Unhide - Alt, O, H, U

Just in case if you don't want any one to go in VBA code and unhide the super hidden sheets. You can protect the VBA module with password of your choice.

Check the post How to protect vba module in excel ? for the same.


Also check out my posts on :

• How to use Visual Basic Editor in Excel VBA ?
• Macro Security in Excel VBA
• How to edit and debug the macro created in Excel V...
• How to use Excel Macro Recorder ?
• How to turn on the Developer Tab in Excel ?

How to split or delimit data using Excel VBA code ?

Using the below macro, you can split the text in one cell. This work as a delimiter, without using the Text to column feature available in excel.

You can use the below code, anywhere in your VBA coding project, where there is a requirement to split the text with specific symbol reference.

Below is the sample code with sample data as example.

Open new excel worksheet.

Copy below data in Cell A2 or Sheet2.

#>ABC>DEF>GHIJ

#>GHI>JKL>MNO

#>PQR>STUV>WXY

#>ZABC>DEFG>HIJK

#>LMN>OPQ>RSTU

#>W>XY>Z>TT

here in the above data we will use ">", symbol to split the data.

open the excel window VBA module. (check out my post on

• How to use Visual Basic Editor in Excel VBA ?)

Insert new module and copy and paste the below VBA code.


Sub split_text()

'--------------------Define Variables which we are using in the macro-----------------------------
Dim indata As String
Dim i As Integer, j As Integer
Dim out As Variant
Dim endrow As Integer
Dim splitSymbol As String

'----------------------Set values to the variables we need to user---------------------------------------

'Set the split symbol, using which we need to split the data
splitSymbol = ">"

'Find the last row of the data in Sheet 2. Using which we will run a loop starting from row number 2 to end row. In older versions of excel update 65536 instead of 1048576
endrow = Sheet2.Range("A1048576").End(xlUp).Row

' There are 2 loops in below code. First is to loop through the rows of the data and second within the cell to split the data

For j = 2 To endrow
   
'assign the data to indata variable, which we need to split. here A acts as column and j as a row number 
        indata = Sheet2.Range("A" & j).Value

 'you can directly put symbol in code or assign the value as listed below out = split(indata, ">")
        out = Split(indata, splitSymbol)
             
 'The split value is assigned to out. While the UBound function below will detect the maximum number from the out string, which we need to split. i.e. this number will define the number of columns in which data will be populated and the below for loop will run for the same number.

       For i = 0 To UBound(out)

'Below code will post the output in the row assigned to j variable and i will act as column
            Cells(j, i + 1) = out(i)
        Next i

Next j

'This will pop up the message box, once the data split is processed. It will say # of rows data processed sucessfuilly
MsgBox j & " rows of data split process completed sucessfully !", vbInformation

End Sub

Please copy the above code to your vba module and test the same. Do let me know if you want any more information on the same.

How to delete the table in MS ACCESS database using Excel VBA code ?

Please add the reference to Microsoft ActiveX Data Objects 2.x Library in the Excel VBA Editor.

see my blog on How to use VBA editor and add references to Excel VBA ?

--------------------------------------------------------------------------------------------------------------------------

Sub Delete_Test_Table ()

'Define the variables to be used in the VBA code

Dim connectdb  As String, pathdb As String, connObj  As ADODB.Connection

'Assigning the database name and the connection path to the pathdb variable

pathdb = Sheet1.Range("A2").Value

'In the above case the reference is taken from Range A2 of sheet 1, while we can directly add the path in VBA code e.g.

'pathdb = "C:\Users\Default\Desktop\Test.accdb"    

'Connection reference and path

connectdb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathdb & ";"

'Connecting to the database and creating new table

Set connObj = New ADODB.Connection

With connObj

.Open connectdb

.Execute "drop table TEST_TABLE"

End With

' Table with name "TEST_TABLE" will be deleted from the database

End Sub

Please note that this function will permanently delete the table and its content, which will be nor more recoverable.

Please use this function with caution.

If you check out my other post regarding, How to create table in MS Access using Excel VBA, you will find that only the .Execute line is changed. Anything you enter withing the double quoted after .Execute "" will be executed on the database by the VBA code.

This concludes that, you can use the above connection method and procedure to execute different queries like UPDATE DATA, DELETE DATA, EXPORT DATA and more.

Create a function to delete only the visible rows after data filter ?

You can copy paste the below function in VBA editor and test it yourself with different columns and criteria.

Function delvisiblerows(col, criteria)

ActiveSheet.Range("$A:$AI").AutoFilter field:=col, Criteria1:= criteria

Set Rng = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range(Cells(2, 1), Cells(1048576, 100)))

If ActiveSheet.AutoFilter.Range.Columns(1).specialcells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
    Rng.EntireRow.Delete
End If

ActiveSheet.AutoFilterMode = False

End Function



How this works ?

ActiveSheet.Range("$A:$AI").AutoFilter field:=col, Criteria1:="<>" & criteria

here we pass the column number and the criteria values from the macro routine.

This is then used around the If statement above. If any row is filtered as per the criteria match, then only the visible rows will be deleted.

The important function to remember here is .specialcells(xlCellTypeVisible).Cells property available in the excel vba.

e.g.
Say you have different user names in column A and you want to delete the data for user "Suresh", you can pass the values in macro as below.

Sub DeleteVisibleRows()

Call delvisiblerows(1, "Suresh")

MsgBox "Data for Suresh Deleted Sucessfully!", vbInformation

End Sub

_________________________________________________________________________________

Happy learning !

Also check out my post on how to find first and last date of any month.