Working With NAMED Ranges in VBA

1. The code to change the value of each cell of the name range at the same time

Sub Change_the_Value_of_Each_cell_in_Named_Range()
    Dim NameRange As Range
    Set NameRange = Range("SALES")
    Dim Eachcell As Range
    For Each Eachcell In NameRange
        Eachcell.Value = "Sales" & "2017"
    Next Eachcell
    MsgBox "Done"
End Sub

2. Let's Say You have a list of Named Range in Sheet3 where you want to change the value of each Named Range with the Same Format

Sub Change_The_Value_of_Each_Named_Range()
    Dim I As Long
    I = 2
    Dim SheetName As String
    SheetName = "Sheet3"
    Dim TargetRange As Range
    Do Until Sheets(SheetName).Cells(I, 1).Value = vbNullString
        Set TargetRange = Range(Sheets(SheetName).Cells(I, 1).Value)
        TargetRange.Value = Sheets(SheetName).Cells(I, 1).Value & VBA.Format(Sheets(SheetName).Range("B6").Value, "YYYY")
    I = I + 1
    Loop
End Sub

3. TO RENAME THE WHOLE NAMED RANGES with EXACT FORMAT

Sub Rename_The_Name_Range()

    Dim SheetName As String
    SheetName = "Sheet3"
    Dim TargetSheet As Worksheet
    Set TargetSheet = Sheets(SheetName)
    Dim TargetNamedRange As Name
    Dim CurrentName As String
    Dim Iname As String
    Dim OriginalNamePlace As Integer
    Dim HelperString As String
    For Each TargetNamedRange In ThisWorkbook.Names
        Iname = TargetNamedRange.Name
        If VBA.InStr(1, Iname, "_") = 0 Then
            CurrentName = Iname & VBA.Format(VBA.Date, "_yyyy")
            ThisWorkbook.Names(Iname).Name = CurrentName
        Else
            OriginalNamePlace = VBA.InStr(1, Iname, "_")
            HelperString = VBA.Left(Iname, OriginalNamePlace - 1)
            CurrentName = HelperString & VBA.Format(VBA.Date, "_yyyy")
            ThisWorkbook.Names(Iname).Name = CurrentName
        End If
    Next TargetNamedRange
    MsgBox "Done"
End Sub

Timer in Excel Using VBA

Gift Your self a Nice Timer in Excel Using VBA

Just Give It A Try!

Step 1: Insert an Userform

Step 2: Locate the SHOWMODAL Property of The Userform and make it FALSE

Step 3: Design the userform you as you are seeing in video or you can design your own. it's up to you.

Step 4: Inert a New Module and paste the following code into it.

Sub ()
UserForm2.Show vbModeless
End Sub

Step 5: Double Click on a button on the userform which will start the clock and paste the following code in it.

Private Sub CommandButton1_Click()
Do
DoEvents
UserForm2.Label1.Caption = VBA.Time
Loop
End Sub

Step 6: Paste the following code to close the Timer
Private Sub CommandButton2_Click()
Unload UserForm2
End Sub

Step 7: Put a Button on the Sheet and assign macro named as "Open_Timer"
and it's done! enjoy friend.

By Kamal Bharakhda

Sheets Navigator Tool in Excel VBA

You all have that one workbook or spreadsheet, which have definitely more than 50 sheets. Which is very boring to scroll sometimes when you want to work with any random sheets more often.

There are many ways we can perform navigation through sheets. We can create the shapes to navigate there but hardly, we can only do that for a few sheets. Not for every sheet. So, here I'm coming with a permanent and handsome solution.

My Sheet Navigator

How it looks?

How it Works?

it's just initiated with pressing "Ctrl+k"

(I have selected "k" as a shortcut key.)

Select the Sheet you want to navigate to, and double-click it. Navigator takes you to that sheet and userform will be closed automatically.

Isn't easy?

Let's develop this Awesome Tool. 

Step 1: Goto the Developer Tab and click on the Record Macro. You will be asked to name the new macro which you gonna record. Now, don't require to name this macro but if you want then do it. Now, most important is that to assign a macro with the Shortcut key. As I have said, in my case I have chosen the letter "k" and you choose what you want. but remember, if you choose the active shortcut key, after this process, that earlier action will be overlapped by the current macro activities

So save the macro and goto the Developer Tab again and Stop the Recording. Now in VB Editor, you will see this module. 

Step 2: Design the Userform. 

You just need one list box! Insert the Userform and pick the list box from the toolbox and set it just like what you can see in the image. 

Step 3: Coding! 

This tool is running totally on two Events. 

1. UserForm_Initialize event 

2. Listbox ListBox_DblClick Event

Here is the Code for UserForm_Initialize event 

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = ThisWorkBook.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    Dim sh As Worksheet

    

    For Each AllSH In Machine.Sheets

        Userfrom1.ListBox1.AddItem (sh.Name)

    Next

    

Application.ScreenUpdating = True

End Sub

Here is the Code for Listbox ListBox_DblClick Event 

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = THEIDP.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    With UserForm1.ListBox1

        Machine.Worksheets(.List(.ListIndex)).Activate

    End With

    

    Unload UserForm1

Application.ScreenUpdating = True

End Sub

There you go...save it and run it! EasyPeasy!!!!

Thank for trying this. 

Regards,

Kamal Bharakhda