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

The completely dynamic way to Rename the Shapes in Excel VBA

Sub Rename_Shape()

Dim SheetName As String
SheetName = "Sheet1"

Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets(SheetName)

WS.Activate

Dim Shp As Shape

Dim ShapeName As String
ShapeName = vbNullString

Dim ShapeSelected As String
Dim NewNameofShape As String

For Each Shp In WS.Shapes
ShapeName = ShapeName & " ' " & Shp.Name & " ' "
Next Shp

ShapeSelected = VBA.InputBox(ShapeName & " Please Input the Shape names in Inverted Comma", "Select Shape")

NewNameofShape = VBA.InputBox("Please enter new name of Shape you have selected", "Rename Shape")

If ShapeSelected = vbNullString Or NewNameofShape = vbNullString Then Exit Sub

WS.Shapes(ShapeSelected).Name = NewNameofShape

End Sub

Points to Remember

1. The First Inpubox will provide you the list of the Shape available in the Inverted Comma. You need to type the name in the input area which you want to rename is

2. A second input box will ask you to enter a new name.

and there you go.

Fill ComboBox based on Seach Criteria

One the best approch for this requirement is to add one textbox as search bar which provides the same result you reuired.

Here we need to focus on three things. I'm cosidering that your combobox is in the userform1
and your students data list is in the sheet1.

Now,

There are two events you need configure to perform this task.

1. UserForm_Initialize
2. TextBox1_Change

and the one sub procedure which will populate the result based on the keyword in the textbox

3. Populate_Combobox

Now

Coding for the 1 & 2 is the same

Private Sub TextBox1_Change()
Populate_Combobox
End Sub

Private Sub UserForm_Initialize()
Populate_Combobox
End Sub

Now let's see the code for the Sub Procedure

Private Sub Populate_Combobox()
Dim I As Long
I = 2
Dim LenofStr As Long
LenofStr = 0
With UserForm1
.ComboBox1.Clear
If .TextBox1.Value = vbNullString Then
Do Until Sheet1.Cells(I, 1).Value = Empty
.ComboBox1.AddItem Sheet1.Cells(I, 1).Value
I = I + 1
Loop
Else
LenofStr = VBA.Len(.TextBox1.Value)
Do Until Sheet1.Cells(I, 1).Value = Empty
If VBA.Left(VBA.UCase(Sheet1.Cells(I, 1).Value), LenofStr) = VBA.UCase(.TextBox1.Value) Then
.ComboBox1.AddItem Sheet1.Cells(I, 1).Value
End If
I = I + 1
Loop
End If
End With
End Sub

I have also attached images with this post

BOLD a Part of the Cell Value

Q:  D1=(A1&B1)...I want the result of B1 Bold when result show i.e. Biplab (A1) & Rs.12/-(B2). In Cell No. C1 there is formula =(A1&" "B1)...Now Result I want "Biplab Rs.12/-"..... Only Rs.12/- is BOLD. (Note: Length of B1 is not fixed)

Let's create a function called Bold which will make any character under the function to bold. and you can use it directly in the sheet using formula feature. 

here is the two thing.

Put the Following function in the Module

Function BOLD(ByRef Text As String) As String

Text = "!" & Text

BOLD = Text

End Function

Now Goto the Sheet and choose we will work on Worksheet_Change Event 

here is the Code. 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim I As Variant

I = Target.Value

Dim A As String

Dim B As String

Dim ALEN As Integer

Dim BLEN As Integer

Dim TargetColumnNo As Integer

TargetColumnNo = 3

If Target.Column = TargetColumnNo Then

If VBA.InStr(1, I, "!") > 0 Then

A = VBA.Left(I, Application.WorksheetFunction.Find("!", I, 1) - 1)

B = VBA.Mid(I, VBA.InStr(1, I, "!") + 1, VBA.Len(I) - VBA.InStr(1, I, "!"))

ALEN = VBA.Len(A)

BLEN = VBA.Len(B)

Target.Value = A & " " & B

Target.Characters(ALEN + 2, BLEN).Font.FontStyle = "Bold"

Else

Exit Sub

End If

End If

End Sub

Now Suppose in the Sheet1 and Column 3 would be your target column then 

write the following formula and you will get the required result. 

=A1&BOLD(B1)

Thank you. 

Replace Excel's Data Validation Drop-down with Smart Userfrom Based Mini Tool

Creating Dropdown from the Validation part of the excel is very easy and very fundamental thing we should know as an Excel Enthusiastic.

When our project required the same list of things to be chosen from the single cell usually make things done with creating the drop-down list from the Data Validation Tool which is inbuilt in Excel.

Now, Sometimes you might have faced or not, but each cell of the column requires the same list of things for selection. I was usually ending up by drag down the dropdown list to the nth row which could be messy sometimes. and it even no looks cool. and you even can't put things apart from the list.

What I have done here is, You don't require a dropdown list at all. Just see the video and you will get what I have done.

The advantage of this tool is you don't need to create hundreds of dropdown in the same column when everything is available to you in just a double-click.

To compile this tool I have required following two scopes.

1. Worksheet Code
2. Userform Design and Codes

You can design the userform and it looks easy not a big deal.

Now let's move towards coding of Worksheet
======
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    If Target.Column = 2 Then
        LISTBOX.Show
    End If
    
End Sub
======
I have used BeforeDoubleClick Event to initiate the things.

Now, as we double click on the cell on column two, the Sheet Code will sense it and trigger the event and will force userform to appear.

More to the Second part of the coding

=====
Private Sub UserForm_Initialize()

    Application.ScreenUpdating = False
    
    Dim DB As Worksheet
    Set DB = Sheets("DATABASE")
        
    Dim I As Long
    I = 1
    
    LISTBOX.ListBox1.Clear
    
    Do Until DB.Cells(I, 2).Value = Empty
        LISTBOX.ListBox1.AddItem DB.Cells(I, 2).Value
    I = I + 1
    Loop

    Application.ScreenUpdating = True

End Sub
=====

(Above code will take each item of the list from the Place called Database Sheet where you have listed all the details over there with Heading.)

Now DOuble Click even to input userform data into the sheet which the same part of userform coding

======
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    With LISTBOX.ListBox1
        If .ListIndex = -1 Then Exit Sub
        ActiveCell.Value = .List(.ListIndex)
        Cells(ActiveCell.Row, ActiveCell.Column + 1).Activate
        Unload LISTBOX
    End With
    
End Sub
======

Now Coding for the Add New Button. this coding will add Item to the list.

Private Sub CommandButton1_Click()
    
    Dim I As Variant
    Dim J As Variant
    
    If ActiveCell.Column = 2 Then
        I = VBA.InputBox("Please Enter New Tail No.:", "Add New Tail No. in The Database")
        If I = vbNullString Then
            Exit Sub
        Else
            J = NewRow("DATABASE", 2)
            Sheets("DATABASE").Cells(J, 2).Value = I
            Dim DB As Worksheet
    Set DB = Sheets("DATABASE")
     
    I = 1
    
    LISTBOX.ListBox1.Clear
    
    Do Until DB.Cells(I, 2).Value = Empty
        LISTBOX.ListBox1.AddItem DB.Cells(I, 2).Value
    I = I + 1
    Loop
        End If
  end if 

end sub
======

So This is it. Please Try. and create more ways to use it in a more effective way.

Thank you

- Kamal Bharakhda

https://www.youtube.com/watch?v=hAEL7TeqOnQ

What is the formula for calculating the inclusive tax amount from a given amount?

Let's answer it with mathematical approach. You might required to use it in the formula or in VBA. This simplification will help you for sure.

Assume few variables

X = Product's Original Amount without Tax

Y = Inclusive Tax Amount (i.e. you're looking)

Z = Product's Amount with Tax or MRP or Included tax amount.

Q = Tax %

-

So basic foundation of product's final amount after tax including is like,

X + Y = Z

Now, Y = ( Q * X ) / 100

So the equation become,

X + ( Q * X ) / 100 = Z

Now solve the above equation to find the value of X

X = ( Z * 100 ) / ( Q + 100 )

And to get the value you required put this value of X back to the initial equation

X + Y = Z

{( Z * 100 ) / ( Q + 100 )} + Y = Z

Now solve for Y,

Y = ( Z * Q ) / ( Q + 100 )

So put known values of Z & Q in the above equation and you will get the required value.

Good day.

Kamal Bharakhda