Display in a textbox, the character count of a cell, when the cell is selected

Can we display character count in a textbox (placed in a sheet), when the cell is selected? Please suggest any better way of achieving this? I want to avoid the usage of button/msgbox.

How can i make this work for any cell i click on a worksheet? Also, i need to know if we can implement for a range of cells say A1:A10. I want to try both these cases.

Well, it depends. If you have just 1 textbox called textbox1 then its easy. If you have multiple textboxes I am going to need a lot more information. This will do range "A1:A10". You should be able to see the differnce to modify yourself.

  
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    TextBox1.Text = Len(Target.Value)
End If

End Sub

Hi! Thanks for the response. I am using only one textbox to display character count of a cell. I tried your code, but somehow it didn't work for me Can you please suggest what i need to modify here?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS2 As Worksheet, LRow As Long
Set WS2 = Worksheets("Sheet2")
LRow = WS2.Range("A" & Rows.Count).End(xlUp).Row
If Not Intersect(Target, Range("F5:F" & LRow)) Is Nothing Then
    TextBox1.Text = Len(Target.Value)
End If
End Sub

Originally Posted by stnkynts

Well, it depends. If you have just 1 textbox called textbox1 then its easy. If you have multiple textboxes I am going to need a lot more information. This will do range "A1:A10". You should be able to see the differnce to modify yourself.

  
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    TextBox1.Text = Len(Target.Value)
End If

End Sub

@stnkynts: Need help! Can you please help me resolve this?

This works just fine for me. Are you sure your using an ActiveX textbox titles TextBox1?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long

LRow = Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range("F5:F" & LRow)) Is Nothing Then
    TextBox1.Text = Len(Target.Value)
End If

End Sub

I used ActiveX textbox not sure why it isn't working for me. I hope i am clear in explaining what i am looking for. if not, I am trying to display the character count in a textbox when i select a cell.

Originally Posted by stnkynts

This works just fine for me. Are you sure your using an ActiveX textbox titles TextBox1?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long

LRow = Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range("F5:F" & LRow)) Is Nothing Then
    TextBox1.Text = Len(Target.Value)
End If

End Sub