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

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

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
 
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:
 
" = 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.
 
I have had very similar issues. It turns out that the newer version of Excel doesn't allow multi-code parsing. Thanks for the pointers Rick, it provided me a starting point to research this issue.

-C.W.
Excel Statement
 
Back
Top