CODEWITHSUNDEEP
excelvba
Shiva
Shiva

Reputation: 430

MS Excel: Show length dynamically on changing cell value

There is a string value in cell A2 which should show its length in cell B2. When I change the string in cell A2 and press enter, the length in cell B2 will also change as per LEN function. But I need length to show "dynamically" when typed in the cell by pressing F2. Below is the Excel VBA that I tried

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("A2:A2")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

    Range("B2").Value = Len(Range(Target.Address))
    End If
End Sub

Upvotes: 0

Views: 254

Answers (1)

mattboy
mattboy

Reputation: 2910

I don't think this is possible when typing in a cell. The closest solution I can think of is to create an ActiveX TextBox instead, then reading the length of its value when it's changed like this:

Private Sub TextBox1_Change()
    Range("A1") = Len(TextBox1.Value)
End Sub

Upvotes: 1

Related Questions