Excel VBA: How to Create a User Form

To Create a User Form in Excel. Press Alt + F11 on your Keyboard.
You will see the below image.

Now go to Insert => User Form

Rename the Form by going to properties
Name as SimpleForm (This name will be used to call from VBA Code)
Caption as Simple Form (This name will be displayed on the Form)

Now Add Three Labels , Text Box and Command Button.

Double Click on Submit Button and add below code.

Private Sub btnSubmit_Click()

'Declare the Variables
Dim DATA As Worksheet
Dim LastRow As Integer

'Assinging DATA as worksheet Object
Set DATA = Worksheets("DATA")

'Finding LastRow in DATA Sheet
LastRow = DATA.Cells.SpecialCells(xlCellTypeLastCell).Row

'Validating all the fields to check all the fields are entered

If txtName.Value = "" Then
MsgBox "Please Enter a Valid Name", , "Simple Form"
Exit Sub
ElseIf txtCity.Value = "" Then
MsgBox "Please Enter a Valid City Name", , "Simple Form"
Exit Sub
ElseIf txtCountry.Value = "" Then
MsgBox "Please Enter a Valid Country Name", , "Simple Form"
Exit Sub
End If

'Copying the data entered in Simple Form to DATA Sheet
DATA.Cells(LastRow + 1, 1).Value = txtName.Value
DATA.Cells(LastRow + 1, 2).Value = txtCity.Value
DATA.Cells(LastRow + 1, 3).Value = txtCountry.Value

'Display a Message
MsgBox "Form has been submitted Successfully", , "Simple Form"

'Resetting all the fields to blank
btnReset_Click

End Sub

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

Double click on Reset Button and add below code.

Private Sub btnReset_Click()

'Resetting all the fields

txtName.Value = ""

txtCity.Value = ""

txtCountry.Value = ""

End Sub

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

Double click on Close Button and add below code.

Private Sub btnClose_Click()

'Hiding Form

SimpleForm.Hide

End Sub

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

Now Go to Insert and Click on Module.

Sub OpenForm()

'Open a Form

SimpleForm.Show

End Sub

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

Now in the DATA Sheet add Shape by going to Insert => Shape.

Right Click on Selected Sheet and Select Assign Macro.

Select Open Form

Now to Start form click on Simple Form.

See it in Action.

Excel VBA: How to Merge Multiple Sheet into One Sheet

Merging more than one sheets in excel may be time consuming. But with the below VBA code it will only take few seconds to complete the task.

Below is the VBA Code.

Sub MergeSheet()

'Declaring the Variables
Dim LastRow, ShtCnt As Integer
Dim ShtName As String
Dim NewSht As Worksheet

'Assinging a Sheet Name by UserInput
ShtName:
ShtName = InputBox("Enter the Sheet Name you want to create", "Merge Sheet", "Master Sheet")

'Count of Total Worksheet in the present workbook
ShtCnt = Sheets.Count

'Using For Loop check if the worksheet exists
For i = 1 To ShtCnt
If Sheets(i).Name = ShtName Then
MsgBox "Sheet already Exists", , "Merge Sheet"
GoTo ShtName
End If
Next i

'Create a New Sheet
Worksheets.Add.Name = ShtName

'Assigning NewSht as Current Sheet
Set NewSht = ActiveSheet

'Moving Worksheet to the beginning of this workbook
NewSht.Move before:=Worksheets(1)

'Copying all the data to the New Sheet Using For Loop
For i = 2 To ShtCnt + 1

'If i=2 Then copy all the data from the second sheet including header.
If i = 2 Then
Sheets(i).UsedRange.Copy NewSht.Cells(1, 1)
Else

'If i is grater than 2 then copy all the data excluding Header(1st Row).
Sheets(i).UsedRange.Offset(1, 0).Resize(Sheets(i).UsedRange.Rows.Count - 1, Sheets(i).UsedRange.Columns.Count).Copy NewSht.Cells(LastRow + 1, 1)
End If
LastRow = NewSht.Cells.SpecialCells(xlCellTypeLastCell).Row
Next i

'Displaying the Message after copying data successfully
MsgBox "Data has been copied to " & ShtName, , "Merge Sheet"

End Sub

See it in Action.

Excel VBA: How to Extract Data from Excel to Text File

In this post we are going to see how to export data from excel to text file using VBA.

Below is the VBA Code.

Sub ExceltoText()

'Declaring the variables
Dim FileName, sLine, Deliminator As String
Dim LastCol, LastRow, FileNumber As Integer

'Excel Location and File Name
FileName = Thisworkbook.path & "\ExceltoText.txt"

'Field Separator
Deliminator = "|"

'Identifying the Last Cell
LastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
FileNumber = FreeFile

'Creating or Overwrighting a text file
Open FileName For Output As FileNumber

'Reading the data from Excel using For Loop
For i = 1 To LastRow
For j = 1 To LastCol

'Removing Deliminator if it is wrighting the last column
If j = LastCol Then
sLine = sLine & Cells(i, j).Value
Else
sLine = sLine & Cells(i, j).Value & Deliminator
End If
Next j

'Wrighting data into text file
Print #FileNumber, sLine
sLine = ""
Next i

'Closing the Text File
Close #FileNumber

'Generating message to display
MsgBox "Text file has been generated"

End Sub

See it in action.

Excel VBA: Finding Last Cell

From the below VBA Code you will be able to find last cell in excel

Sub LastCell()  'Name of a Code
'Declaring the Variables
Dim LastCell As Integer 'Declaring the Variables

'Performing All VBA Action in the Active Sheet
With ActiveSheet

'Finding Last Row in a Sheet
LastCell = .Cells.SpecialCells(xlCellTypeLastCell).Row

'Displaying Message
MsgBox LastCell, , "LastRowinaSheet"

'Finding Last Column in a sheet
LastCell = .Cells.SpecialCells(xlCellTypeLastCell).Column

'Displaying Message
MsgBox LastCell, , "LastColumninaSheet"

'Finding Last Row in a Sigle Colum
LastCell = .Cells(.Cells.Rows.Count, "F").End(xlUp).Row

'Displaying Message
MsgBox LastCell, , "LastRowinaSingleColumn"

'Finding Last Column in a Single Row.
LastCell = .Cells(7, .Cells.Columns.Count).End(xlToLeft).Column

'Displaying Message
MsgBox LastCell, , "LastColumninaSingleRow"

'Finding Last Row using UsedRange
LastCell = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'Displaying Message
MsgBox LastCell, , "LastRowUsingUsedRange"

'Finding Last Column using UsedRange
LastCell = .UsedRange.Columns(.UsedRange.Columns.Count).Column

'Displaying Message
MsgBox LastCell, , "LastColumnUsingUsedRange"

End With

End Sub