Excel VBA Lesson 2 Message Box

Excel VBA Lesson 1(a) Message Box

Syntax

MsgBox(prompt[,buttons][,title][,helpfile,context])

Message Box type

vbOKOnly Shows only the OK button

vbOKCancel Shows the OK and Cancel buttons

vbAbortRetryIgnore Shows the Abort, Retry, and Ignore buttons

vbYesNo Shows the Yes and No buttons

vbYesNoCancel Shows the Yes, No, and Cancel buttons

vbRetryCancel Shows the Retry and Cancel buttons

vbMsgBoxHelpButton Shows the Help button. For this to work, you need to use the help and context arguments in the MsgBox function

Example:-

Sub MsgBoxYesNo()
MsgBox "Should we stop?", vbYesNo
End Sub

 

Read More

Excel VBA Lesson 4 Range

Excel VBA Lesson 4 Range

Activate

AddComment

AdvancedFilter

Calculate

Clear

ClearComments

ClearContents

ClearFormats

ClearHyperlinks

Copy

CopyFromRecordset

Cut

Delete

Find

Insert

Merge

PasteSpecial

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

VBA Activate Range – Examples

Sub Activating_Single_Cell()

    Range("A2").Activate

    'or

    Cells(2, 1).Activate

End Sub

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

Sub Activating_multiple_Cells()

Range("A2:C3").Activate

End Sub

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

Sub Activating_multiple_Cells()

Range(Cells(2, 1), Cells(3, 3)).Activate

End Sub

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

VBA Add Comments to Excel Range -

Range("A2").AddComment "Hello, this is a comment text"

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

Excel VBA Range Advanced Filter-

Sub VBA_Range_Advanced_Filter()

    Range("A3:B8").AdvancedFilter Action:=xlFilterInPlace, _

    CriteriaRange:=Range("B1:B2"), Unique:=False

End Sub

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

VBA Range Calculate – 

Sub VBA_Calculate_Range()

    Range("A2:D10").Calculate

End Sub

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

VBA Clear Range in Excel explained 

 Range("A2:D10").Clear

Excel VBA to ClearComments in a Range

 Range("A2:D10").ClearComments 

Excel VBA to ClearContents in a Range

Range("A2:D10").ClearContents

Excel VBA to ClearFormats in a Range

Range("A2:D10").ClearFormats 

Excel VBA to ClearHyperlinks in a Range

Range("A2:D10").ClearHyperlinks

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

VBA to Copy Range in Excel 

Sub Copy_Range_To_Clipboard()

Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard

'Now you can select any range and paste there

Range("E2").Select

ActiveSheet.Paste

End Sub

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

VBA to Copy Range to Destination in Excel

Sub Copy_Range_To_Destination_Range()

Range("A2:D10").Copy Range("E2")

'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")

End Sub

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

VBA to Cut Range in Excel 

Sub Cut_Range_To_Clipboard()

Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard

'Now you can select any range and paste there

Range("E2").Select

ActiveSheet.Paste

End Sub

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

VBA to Cut and paste the Range to Destination in Excel

Sub Cut_Range_To_Destination_Range()

Range("A2:D10").Copy Range("E2")

'Or you can write as Range("A2:D10").Cut Destination:=Range("E2")

End Sub

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

VBA to Copy Range in Excel –

Sub Copy_Range_To_Clipboard()

Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard

'Now you can select any range and paste there

Range("E2").Select

ActiveSheet.Paste

End Sub

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

VBA to Copy Range to Destination in Excel 

Sub Copy_Range_To_Destination_Range()

Range("A2:D10").Copy Range("E2")

'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")

End Sub

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

VBA to Copy Range in Excel

Sub Copy_Range_To_Clipboard()

Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard

'Now you can select any range and paste there

Range("E2").Select

ActiveSheet.Paste

End Sub

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

VBA to Copy Range to Destination in Exce

Sub Copy_Range_To_Destination_Range()

Range("A2:D10").Copy Range("E2")

'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")

End Sub

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

VBA to Cut Range in Excel

Sub Cut_Range_To_Clipboard()

Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard

'Now you can select any range and paste there

Range("E2").Select

ActiveSheet.Paste

End Sub

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

VBA to Cut and paste the Range to Destination in Excel

Sub Cut_Range_To_Destination_Range()

Range("A2:D10").Copy Range("E2")

'Or you can write as Range("A2:D10").Cut Destination:=Range("E2")

End Sub

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

VBA to Delete Range in Excel 

Sub Delete_Range_To_ShiftLeft()

    Range("B2:D10").Delete Shift:=xlToLeft

End Sub

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

VBA to Delete Range in Excel – Example:Shift:=xlToUp

Sub Delete_Range_To_ShiftUp()

Range("B2:D10").Delete Shift:=xlToUp

End Sub

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

VBA to Delete Range in Excel – Example: EntireRow

Sub Delete_Range_EntireRow()

Range("B2:D10").EntireRow.Delete 

End Sub

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

VBA to Delete Range in Excel – Example: EntireColumn

Sub Delete_Range_EntireColumn()

    Range("B2:D10").EntireColumn.Delete 

End Sub

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

VBA to Find Value in a Range 

Sub Find_Range()

    MsgBox Range("B2:D10").Find(What:="ab").Address

End Sub

VBA to Find Value in a Range –

Sub Find_Range_After()

    MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address

End Sub

VBA to Find Value in a Range – MatchCase

Sub Find_Range_CaseSensitive()

    MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address, MatchCase:=True

End Sub

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

VBA Insert Range in a Worksheet – xlDown

Range("C7").Insert Shift:=xlDown

VBA Insert Range in a Worksheet – xlToRight

Range("C7").Insert Shift:=xlToRight

VBA Insert Range in a Worksheet – EntireRow

Range("B2:D10").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

VBA Insert Range in a Worksheet – EntireColumn

  Range("B2:D10").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

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

VBA Merge Range – 

Range("A1:D10").Merge

VBA Merge Range – Example2

  Range("A1:D10").Merge(True)

VBA Merge Rows-

Range("5:10").Merge

VBA Merge Column –

Range("B:E").Merge

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

VBA PasteSpecial method of Range –

Sub Range_PasteSpecial_Values()

    Range("C6:D11").Copy

    Range("G6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

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

Paste Special properties

xlPasteValues

xlPasteComments

xlPasteFormulas

xlPasteFormats

xlPasteAll

xlPasteValidation

xlPasteAllExceptBorders

xlPasteColumnWidths

xlPasteFormulasAndNumberFormats

xlPasteValuesAndNumberFormats

xlPasteAllUsingSourceTheme

xlPasteAllMergingConditionalFormats

xlPasteSpecialOperationAdd

xlPasteSpecialOperationSubtract

xlPasteSpecialOperationMultiply

xlPasteSpecialOperationDivide

xlPasteSpecialOperationNone

Code

How to Range  value assign.

Range("A1").Value = "Hello"

How to Range  value assign . With using application , workbooks and worksheets object

Application.Workbooks("create-a-macro").Worksheets(1).Range("A1").Value = "Hello"

Using the worksheet name.

Worksheets("Sales").Range("A1").Value = "Hello"

Using the index number (1 is the first worksheet starting from the left).

Worksheets(1).Range("A1").Value = "Hello"

3. Using the CodeName.

Sheet1.Range("A1").Value = "Hello"

Value Put in range

Range("B3").Value = 2

Value Put in Multiple range

Range("A1:A4").Value = 5

Below Range Assign and Interior Color Green

Dim Rng As Range

Set Rng = Selection

Selection.Interior.Color = vbGreen

Value Put in Multiple range

Range("A1:A2,B3:C4").Value = 10

Range Value copy

Range("C3:C4").Value = Range("A1:A2").Value

Range("A1").ClearContents

Range("A1").Value = ""

Range CurrentRegion

Range("A1").CurrentRegion.Select

Resize

Range("A1:C4").Resize(3, 2).Select

value put B3 Cell

Cells(3, 2).Value = 2

value put A1 AND D1 Cell

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Read More

Excel VBA Lesson 3 Worksheet | Excel VBA Worksheet Object

VBA  Worksheet Method:-

Activate
Copy
Delete
Move
Protect
Select
UnProtect
---------------------------------------------------------------------------------

VBA Activate Worksheet
Sub Activate_Sheet()
  Worksheets("Project1").Activate
'Or
Sheets("Project1").Activate
End Sub

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

Example-2
Sub Activate_Sheet_BasedOnIndex()
Worksheets(1).Activate
'Or
Sheets(1).Activate
End Sub
----------------------------------------------------------------------------

VBA Copy Worksheet Method –Before: Example 1

Sub CopySheet_Beginning()
Worksheets("Sheet3").Copy Before:=Worksheets(1)
End Sub

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

Sub CopySheet_Beginning1()
 ActiveSheet.Copy Before:=Worksheets(1)
End Sub

----------------------------------------------------------------------------------
Sub CopySheet_End()
Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count)
End Sub

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

Sub CopySheet_End()
 ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
End Sub

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

VBA Delete Worksheet Method: Example
Sub Delete_Sheet()
Sheets("Sheet2").Delete
 ‘Or
Sheet2.Delete
End Sub

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

Sub Delete_ActiveSheet()
 ActiveSheet.Delete
End Sub

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

Sub Delete_Sheet_WithoutWarningMessage()
 Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True
End Sub

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

VBA Move Worksheet Method: Before
Sub MoveSheet_Beginning()
Worksheets("Sheet3").Move Before:=Worksheets(1)
End Sub

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

Sub MoveSheet_Beginning1()
ActiveSheet.Move Before:=Worksheets(1)
End Sub

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

Sub MoveSheet_End()
Worksheets("Sheet3").Move After:=Worksheets(Worksheets.Count)
End Sub

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

Sub MoveSheet_End()
 ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub

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

VBA Protect Worksheet Method – Without Password
Sub Protect_WorkSheet_Without_Password()
 Sheets("Sheet1").Protect
End Sub
-----------------------------------------------------------------------

VBA Protect Worksheet Method – With Password
Sub Protect_WorkSheet_With_Password()
Sheets("Sheet1").Protect "YourPassword", True, True
End Sub
-----------------------------------------------------------------------
VBA Select Worksheet Method
Sub Select_Range()
 Worksheets("Project1").Activate
 Range("A1").Select
End Sub
----------------------------------------------------------------------
VBA Select Worksheet Method: Example 2
Sub Select_Range()
Worksheets(1).Activate
Range("A1").Select
End Sub
------------------------------------------------------------------------
VBA UnProtect Worksheet Method –
Sub Unprotect_WorkSheet_Without_Password()
Sheets("Sheet1").Unprotect
End Sub
------------------------------------------------------------------------
Sub Unprotect_WorkSheet_With_Password()
Sheets("Sheet1").Unprotect "YourPassword"
End Sub

Read More

Excel VBA Lesson 2- Workbook | Excel VBA Workbook Object

 Excel VBA Workbook Object Methods
Activate
Close
Protect
ProtectSharing
RefreshAll
RejectAllChanges
RemoveUser
Save
SaveAs
SaveCopyAs
UnProtect
UnProtectSharing

Method :- Active Example
Workbooks("Project1").Activate
Workbooks(2).Activate
Workbooks("Book3").Activate
Method :- Close Example
ActiveWorkbook.Close   

Example 2:-
Workbooks (“ D:\Sample.xlsx").Close Savechanges:=False    

Method :- Protect Example
ActiveWorkbook.Protect Password:="YourPassword", Structure:=True, Windows:=True

Method :- Protect Share Example
ActiveWorkbook.protectSharing Password:=”YourPassword”, SharingPassword:=”SharedPassword”

Method :- Refresh All Example
Workbooks(“Your Workbook Name”).RefreshAll
VBA RefreshAll Workbook Method-2
ActiveWorkbook.RefreshAll

VBA RefreshAll Workbook Method-3
Workbooks("Sample.xls").RefreshAll
It will refreshes the second workbook.
Workbooks(2).RefreshAll
-----------------------------------------------------------------------------
VBA RemoveUser Workbook:Example 1

Sub Remove_User1()
    Dim UsrList()
    UsrList = ThisWorkbook.UserStatus

    If UBound(UsrList) > 1 Then

        ThisWorkbook.RemoveUser (2)

    End If    

End Sub
----------------------------------------------------------------------------------

VBA Save Workbook: Example 1
Active Workbook.Save

VBA Save Workbook: Example 2

Sub Save_Workbook()
    Dim Wkb As Workbook
 Set Wkb = Workbooks.Add
 Wkb.Save
  End Sub

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

VBA SaveAS Workbook  Example 1

Sub SaveAs_Workbook()

    Dim Wkb As Workbook

    Set Wkb = Workbooks.Add

    ActiveWorkbook.SaveAs Filename:="C:\Sample.xlsm"    

End Sub

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

VBA SaveCopyAS Workbook:Example 1

Sub Workbook_SaveCopyAs()

    ThisWorkbook.SaveCopyAs ThisWorkbook.Name & "_Ver1"

End Sub

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

VBA Unprotect Workbook: Example 1

Sub Unprotect_Sheet()

    ActiveWorkbook.Unprotect “YourPassword”, True, True

End Sub

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

VBA UnProtectSharing Workbook:Example 1

Sub Unprotect_Sheet()

    ActiveWorkbook.UnprotectSharing “YourPassword”

End Sub

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

VBA RemoveUser Workbook:Example 2

Sub Remove_User2()

    Dim UsrList()

    UsrList = ThisWorkbook.UserStatus

    For i = 1 To UBound(UsrList)

        ThisWorkbook.RemoveUser (i)

    Next

End Sub

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

Read More

Excel VBA Lesson 1 - Application Object | Excel VBA Application Object

Excel VBA Application Object:-

TO DISPLAY Scroll Bars

Application.DisplayScrollBars = True

TO HIDE Scroll Bars

Application.DisplayScrollBars = False

TO DISPLAY Status Bar

Application.DisplayStatusBar= True

TO HIDE Status Bar

Application.DisplayStatusBar= False

To remove last copied data from clipart

Application.CutCopyMode = False

To make excel window as invisible

Application.Visible = False

To make excel window as visible

Application.Visible = True

Read More

Excel VBA Lesson | Excel VBA Tutorial |Excel VBA Notebook

 Excel VBA Lesson | Excel VBA Tutorial |Excel VBA Notebook

Lesson 1:-Application

Lesson 2:- Workbook

Lesson 3:- Worksheets

Lesson 4 :- Range

Lesson 5 :- Selection

Read More