Select ALL cells problem and advice please

[jhg1226]

Hi, and thanks for taking the time to view this post.

I am running the following code to create 20 or thirty message boxes that activate when particular cells are clicked. Essentially, I need to provide the user information related to specific cell entries. My problem is when the user selects the upper left cell that selects all cell on the worksheet, I get a "Run-time error '6': Overflow error".

How can I get around this problem? Second question is: Am I going about this the right way? Keep in mind I am a novice and do not want to make this too complicated.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal target As Range)
'Service


Application.ScreenUpdating = False

If Selection.Count = 1 Then
If Not Intersect(target, Range("A19")) Is Nothing Then
MsgBox "Service prior to 9-7-80 - At least 1 AND more message" & vbCrLf & _

"90 days service." & vbCrLf & vbCrLf & _

"Service after More of this message AND 24 months active service " & _

"OR served full period to which he/she was called” & vbCrLf & vbCrLf & _
"Exceptions would go here." & vbCrLf & vbCrLf & _
"Check Chart for appropriate dates." & vbCrLf & vbCrLf & _
"***Claims should be reviewed." & vbCrLf & vbCrLf & _

"If doesn't have qualifying service more of message." & vbCrLf & vbCrLf & _

"No further development is needed.***"

End If

'Presumptive Entitlement
If Selection.Count = 1 Then
If Not Intersect(target, Range("A17")) Is Nothing Then
MsgBox "More of the message " & vbCrLf & _

" Part of message:” & vbCrLf & vbCrLf & _
" Age 65 or older, OR " & vbCrLf & vbCrLf & _
" A patient in a nursing home, OR" & vbCrLf & vbCrLf & _
" Disabled with more of the message. " & vbCrLf & vbCrLf & _

" More of message. "
End If

End If
End If
End Sub



Thanks!!!

 

[Norie]

Which line of code causes the error?

Does changing Selection.Count to Target.Count make any difference?

PS Is this all the code?

 

[jhg1226]

You are correct in that the line causing the problem is Selection.count, but changing it to target.count does not help.

Yes, this is all the code. Thanks for responding!

 

[Formular]

May be Target.Areas.Count will help.

 

[shg]

Change Selection.Count to Selection.CountLarge

 

[jhg1226]

THANK YOU!!! Works like a charm and so easy to fix.

I really appreciate your help!

 

[shg]

You're welcome.