February 15, 2019

How to Display Data on User Form Controls

How to display data on user form controls like folder path, workbooks in the folder, worksheets in folder, add new workbook and add sheets to the new workbook and sort the added worksheets. A user wants to do all these actions from the comfort of a user-form. Watch the training video:

How to Display Data on User Form Controls

Here’s the complete VBA code to get data and perform actions through a user form:

Private Sub UserForm_Initialize()
Dim strFolder As String
strFolder = “C:\invoices\”
TextBox1.Value = strFolder

Dim objfs As Object, objf As Object, objf1 As Object, objfc As Object
Set objfs = CreateObject(“Scripting.FileSystemObject”)
Set objf = objfs.GetFolder(strFolder)
Set objfc = objf.Files


For Each objf1 In objfc

ComboBox1.AddItem objf1.Name

Next objf1

End Sub

Private Sub ComboBox1_Change()
Dim sht As Worksheet, myString As String

myString = ComboBox1.Text
Workbooks.Open Filename:=TextBox1 & myString

For Each sht In ActiveWorkbook.Worksheets
ComboBox2.AddItem sht.Name
Next sht

ActiveWorkbook.Close savechanges:=False

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub TextBox2_AfterUpdate()

Set NewBook = Workbooks.Add

With NewBook

.Title = “Employee Details”

.Subject = “Employees”

.SaveAs Filename:=TextBox1 & TextBox2.Text

End With

End Sub

Private Sub TextBox3_AfterUpdate()
Worksheets.Add after:=Sheet1, Count:=Val(TextBox3)
Application.ScreenUpdating = False
Dim shtCount As Integer, i As Integer, j As Integer
Dim Response As VbMsgBoxResult
Response = MsgBox(“Select Yes for Ascending and No for Descending sort order”, vbYesNoCancel)

shtCount = Sheets.Count

For i = 1 To (shtCount – 1)
For j = i + 1 To shtCount
If Response = vbYes Then
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then Sheets(j).Move before:=Sheets(i) End If ElseIf Response = vbNo Then If UCase(Sheets(j).Name) > UCase(Sheets(i).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
End If
Next j
Next i

Application.ScreenUpdating = True
End Sub


Create New WorkBook

Sort Worksheet Tabs Automatically

Advanced User Form

Watch this video on YouTube.

Download a sample file for practice:

2 thoughts on “How to Display Data on User Form Controls

  1. Hi Dinesh,

    I hope you are doing good!

    I have 5 reports,

    Asl report
    Exception report
    Account report
    Category report
    Spend report

    Pick up the mail based on the subject and I will route it share point.

    For each report should be placed in different share point location.

    Daily I’m receiving an email for same subject ‘Report’ along with a current date. This mail is used for audit purpose so we don’t want to lose the mail.. For that only we are trying to save the mail in share point.

    Paulpandi Murugesan:
    Like wise I have five more reports. If I did one then I can easily do for remaining.

    Kindly help me the code for the same.


  2. Hi Sir
    Your video How to Display Data on User Form Controls is awesome.
    Learn alot from you. FYI, I doing a class lab equipment management for my class. So I wanted to let class user to view what are the items and quantities are availble in the balance of inventory worksheet. Therefore could you advice how could I display the whole inventory balance worksheet out when class user clicks on the display combo box , showing the full page of the balance inventory. Thanks In Advance.

Comments are closed.