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:

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: