Need help in excel with "Statement invalid outside Type block. " error
 

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

Need help in excel with "Statement invalid outside Type block. " error
 

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

Need help in excel with "Statement invalid outside Type block. " error
 

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?:confused:


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

Need help in excel with "Statement invalid outside Type block. "error
 

" = 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?:confused:

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

--

Dave Peterson