Thursday, February 20, 2020

How to protect selected cells in vba

    Please paste these lines in VBA macro.

 'To protect the cells
    Worksheets(DestWS).Range("A9:F75").Locked = True ' To lock
    Worksheets(DestWS).Range("G9:t75").Locked = False ' To edit and unlock
    ActiveSheet.Protect

How to use switch case in VBA

        
 Please paste these lines in VBA macro.

Select Case nowMonth
       
        Case 1
       
        Columns("H:K").EntireColumn.Hidden = False
       
        Case 2
        Columns("L:O").EntireColumn.Hidden = False
       
        Case 3
        Columns("P:S").EntireColumn.Hidden = False
       
        Case 4
        Columns("T:W").EntireColumn.Hidden = False
       
        Case 5
        Columns("X:AA").EntireColumn.Hidden = False
       
        Case 6
        Columns("AB:AE").EntireColumn.Hidden = False
       
        Case 7
        Columns("AF:AI").EntireColumn.Hidden = False
       
        Case 8
        Columns("AJ:AM").EntireColumn.Hidden = False
       
        Case 9
        Columns("AN:AQ").EntireColumn.Hidden = False
       
        Case 10
        Columns("AR:AU").EntireColumn.Hidden = False
       
        Case 11
        Columns("AV:AY").EntireColumn.Hidden = False
       
        Case 12
        Columns("AZ:BC").EntireColumn.Hidden = False
       
        End Select

How to get current month details using VBA

 Please paste these lines in VBA macro.



Dim nowMonth As Integer
nowMonth = Month(Now)

How to split string using vba

 Please paste these lines in VBA macro.

Dim Team As String
Dim LArray() As String

Team = "Others Others Others Others"
LArray() = Split(Team, " ") Or
LArray() = Split(Team) ' enough for space , if any other delimitter, please use above syntax

How to get input from user using vba


 Please paste these lines in VBA macro.
Dim Team As String

Team = Trim(InputBox("Give atleast 4 Team members name ", "Hi", "A B C Others"))

How to Freeze and Unfreeze pane using VBA

 Please paste these lines in VBA macro.

    'To Freeze Panes activate
    Worksheets(DestWS).Activate
    ActiveWindow.FreezePanes = False
    Range("H9").Select 'Temp
    ActiveWindow.FreezePanes = True

How to copy one sheet to another using vba


 Please paste these lines in VBA macro.
        Sheets(SourceWS).Range("A1:G99").Copy Destination:=Sheets(DestWS).Range("A1") 'Temp
        Application.CutCopyMode = False

How to get current workbook worksheet name using vba

 Please paste these lines in VBA macro.

Dim DestWS As String
Dim DestWB As String

DestWB = ThisWorkbook.Name
DestWS = ActiveSheet.Name

How to hide Unhide Rows and Columns using VBA

 Please paste these lines in VBA macro.

    Columns("H:BC").EntireColumn.Hidden = False 'Temp
    Rows("5:64").EntireRow.Hidden = False 'Temp

How to clear Chart in Excel using VBA

 Please paste these lines in VBA macro.

    Dim chtObj As ChartObject
    For Each chtObj In ActiveSheet.ChartObjects
    chtObj.Delete
    Next