I have a cell that I want to display a message box for. The cell
should not contain more than 28 characters. If it does I want to
display a message box that tells the user how many characters they
entered and how many characters to remove from their entry. This is
my code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cce17 As Double
If Not Application.Intersect(Target, Range("E17")) Is Nothing Then
cce17 = Len(E17)
If cce17 28 Then
MsgBox("You have entered a value in this field that is" &
cce17 & " characters in length. You will need to shorten your entry
by " & 28 - cce17 & "characters.", vbAbortRetryIgnore, "InvalidEntry
") As VbMsgBoxResult
End If
End If
End Sub

When I try to compile the code I get an error that says
Compile error:
Statement invalid outside Type block.


I know this is probably simple but I am a beginner to VBA and have
been unable to figure it out.

Thanks,
Brent Blevins

Brent,

Get rid of the "As VbMsgBoxResult" in your MsgBox line of code,
and remove the parentheses from that line.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brent" wrote in message
om...
I have a cell that I want to display a message box for. The
cell
should not contain more than 28 characters. If it does I want
to
display a message box that tells the user how many characters
they
entered and how many characters to remove from their entry.
This is
my code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cce17 As Double
If Not Application.Intersect(Target, Range("E17")) Is Nothing
Then
cce17 = Len(E17)
If cce17 28 Then
MsgBox("You have entered a value in this field that
is" &
cce17 & " characters in length. You will need to shorten your
entry
by " & 28 - cce17 & "characters.", vbAbortRetryIgnore,
"InvalidEntry
") As VbMsgBoxResult
End If
End If
End Sub

When I try to compile the code I get an error that says
Compile error:
Statement invalid outside Type block.


I know this is probably simple but I am a beginner to VBA and
have
been unable to figure it out.

Thanks,
Brent Blevins

I made the changes you suggest. Now moy code looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cce17 As Double
If Not Application.Intersect(Target, Range("E17")) Is Nothing Then
cce17 = Len(E17)
If cce17 28 Then
MsgBox(You have entered a value in this field that is &
cce17 & characters in length. You will need to shorten your entry by
& 28 - cce17 & characters., vbAbortRetryIgnore, "InvalidEntry ")
End If
End If
End Sub

And it will not compile. I get a syntax error.

Did I miss something?


---
Message posted from http://www.ExcelForum.com/

" = quotes
() = parentheses

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cce17 As Double
If Not Application.Intersect(Target, Range("E17")) Is Nothing Then
cce17 = Len(Target.Value)
If cce17 28 Then
MsgBox "You have entered a value in this field that is " _
& cce17 & " characters in length. " _
& "You will need to shorten your entry by " & cce17 - 28 _
& " characters.", vbAbortRetryIgnore, "InvalidEntry"
End If
End If
End Sub

But I would think that this would be under the worksheet_change event--don't
check when the user changes the selection, check when they hit the enter key.




"rcuatman <" wrote:

I made the changes you suggest. Now moy code looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cce17 As Double
If Not Application.Intersect(Target, Range("E17")) Is Nothing Then
cce17 = Len(E17)
If cce17 28 Then
MsgBox(You have entered a value in this field that is &
cce17 & characters in length. You will need to shorten your entry by
& 28 - cce17 & characters., vbAbortRetryIgnore, "InvalidEntry ")
End If
End If
End Sub

And it will not compile. I get a syntax error.

Did I miss something?

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson