Formula in comment or input message

[Little19]

Is it possible to display the result of a formula in an input message or
comment?

I have a cell that has a limit on the number of cahracters and I would like
to show how many characters have been typed as they type.

 

[Jim Thomlinson]

That one is not really possible. There is no internal functionallty that does
that and macros do not operate while a cell is in edit mode.

The best options that I can give you are to use a non-proportional font like
courier new where every character is the same size and then just size your
cell accordingly. The other option would be to use a text box where you will
have greater control over the data entry.

 

[Gord Dibben]

If you want to truncate the typed in text to a certain amount you can use
event code to return a specified number of characters after user hits ENTER
key.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) > 20 Then 'edit 20 to suit
.Value = Left(.Value, 20) 'edit 20 to suit
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP