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

ComboBox1.Clear

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
ComboBox2.Clear
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
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

References:

Create New WorkBook

Sort Worksheet Tabs Automatically

Advanced User Form

Watch this video on YouTube.

Download a sample file for practice:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought 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.

    Thanks,
    Paul

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.