Formula in comment or input message
 

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.

Formula in comment or input message
 

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.
--
HTH...

Jim Thomlinson


"Little19" wrote:

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.

Formula in comment or input message
 

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


On Mon, 28 Sep 2009 10:02:03 -0700, Little19
wrote:

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.