ONE LINE VBA CODE

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

 

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


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

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

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


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

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


About Me

author Click here for Connect FB

Click here for Linkedin