using Excel 2003

I limit text input into a cell location to a certain length

I can only test for this length restriction after they press enter following
data input

if the length is exceeded, using data validation -
I have created an error message box to tell user of the length violation

I'd like to include (within the Error Title) what number of typed characters
was attempted to be entered, so the user knows how many characters they need
to reduce their response by

How do I include a calculated value into the error title

how do I reference the current cell for use in the calculation

I'm familiar with Access references but not how it is accomplished in Excel
--
Jim

You can't include calculations/cell references in the validation error
message

You could instead use an event macro something like this (put it in your
worksheet code module: right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cnMax As Long = 10
Const csMsg As String = _
"Your entry in * is too long" & vbNewLine & _
"Max characters: ^" & vbNewLine & _
"Your character count: $"
Dim nLen As Long
With Target
If Not Intersect(.Cells, Range("J1")) Is Nothing Then
nLen = Len(.Text)
If nLen cnMax Then _
MsgBox Replace(Replace(Replace( _
csMsg, "*", .Address(False, False)), _
"^", CStr(cnMax)), "$", CStr(nLen))
End If
End With
End Sub

For a comprehensive solution, you'd want to modify this to account for
multiple cells in a selection, but it should give you a start.

In article ,
JASelep wrote:

using Excel 2003

I limit text input into a cell location to a certain length

I can only test for this length restriction after they press enter following
data input

if the length is exceeded, using data validation -
I have created an error message box to tell user of the length violation

I'd like to include (within the Error Title) what number of typed characters
was attempted to be entered, so the user knows how many characters they need
to reduce their response by

How do I include a calculated value into the error title

how do I reference the current cell for use in the calculation

I'm familiar with Access references but not how it is accomplished in Excel

How would you replace the msgbox Title from "Microsoft Excel" to something
else?
--
Jim


"JE McGimpsey" wrote:

You can't include calculations/cell references in the validation error
message

You could instead use an event macro something like this (put it in your
worksheet code module: right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cnMax As Long = 10
Const csMsg As String = _
"Your entry in * is too long" & vbNewLine & _
"Max characters: ^" & vbNewLine & _
"Your character count: $"
Dim nLen As Long
With Target
If Not Intersect(.Cells, Range("J1")) Is Nothing Then
nLen = Len(.Text)
If nLen cnMax Then _
MsgBox Replace(Replace(Replace( _
csMsg, "*", .Address(False, False)), _
"^", CStr(cnMax)), "$", CStr(nLen))
End If
End With
End Sub

For a comprehensive solution, you'd want to modify this to account for
multiple cells in a selection, but it should give you a start.

In article ,
JASelep wrote:

using Excel 2003

I limit text input into a cell location to a certain length

I can only test for this length restriction after they press enter following
data input

if the length is exceeded, using data validation -
I have created an error message box to tell user of the length violation

I'd like to include (within the Error Title) what number of typed characters
was attempted to be entered, so the user knows how many characters they need
to reduce their response by

How do I include a calculated value into the error title

how do I reference the current cell for use in the calculation

I'm familiar with Access references but not how it is accomplished in Excel

in your example if instead of cell J1, I had a list of seperate unrelated
cells that this same validation would apply to

would I use a statement like In("j1", "K12",...
With Target?
--
Jim


"JE McGimpsey" wrote:

You can't include calculations/cell references in the validation error
message

You could instead use an event macro something like this (put it in your
worksheet code module: right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cnMax As Long = 10
Const csMsg As String = _
"Your entry in * is too long" & vbNewLine & _
"Max characters: ^" & vbNewLine & _
"Your character count: $"
Dim nLen As Long
With Target
If Not Intersect(.Cells, Range("J1")) Is Nothing Then
nLen = Len(.Text)
If nLen cnMax Then _
MsgBox Replace(Replace(Replace( _
csMsg, "*", .Address(False, False)), _
"^", CStr(cnMax)), "$", CStr(nLen))
End If
End With
End Sub

For a comprehensive solution, you'd want to modify this to account for
multiple cells in a selection, but it should give you a start.

In article ,
JASelep wrote:

using Excel 2003

I limit text input into a cell location to a certain length

I can only test for this length restriction after they press enter following
data input

if the length is exceeded, using data validation -
I have created an error message box to tell user of the length violation

I'd like to include (within the Error Title) what number of typed characters
was attempted to be entered, so the user knows how many characters they need
to reduce their response by

How do I include a calculated value into the error title

how do I reference the current cell for use in the calculation

I'm familiar with Access references but not how it is accomplished in Excel

Take a look at the "MsgBox Function" entry in VBA Help.

In article ,
JASelep wrote:

How would you replace the msgbox Title from "Microsoft Excel" to something
else?

Change

Range("J1")

to

Range("J1,K12")



In article ,
JASelep wrote:

in your example if instead of cell J1, I had a list of seperate unrelated
cells that this same validation would apply to

would I use a statement like In("j1", "K12",...
With Target?