MsgBox for a range and not one cell change please:

[ste mac]

Hi, I got this code from this ng (apologies for not knowing the programmer)
but I would like it to incorporate a range, as it only does one cell as it is
now, such as A1:A10 or A1:F10

thanks for any help you can give

seeya ste

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
If ActiveCell.HasFormula Then
MsgBox ("Please do not type in this cell, Thankyou..")
Else
End If
End If
End Sub

 

[Frank Kabel]

Hi
change the line
If Target.Address = "$A$1" Then

to
If Not Intersect(Range("A1:A10"), Target) Is Nothing Then

 

[Bob Phillips]

also change

If ActiveCell.HasFormula Then

to

If Target.HasFormula Then


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul B]

And if you every want it for the whole sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.HasFormula Then
MsgBox ("Please do not type in this cell, Thankyou..") 'Or replace
this line with your macro
Else
End If
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Frank Kabel]

Hi Bob
goot spot

Additional note for the OP: Why don't you lock these cells and protect
the worksheet?

 

[Dave Peterson]

You could do something like this:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range
Set myRng = Me.Range("c3,b9,D12:e45")

If Target.Cells.Count > 1 Then
Application.EnableEvents = False
Target(1).Select
Set Target = Target(1)
Application.EnableEvents = True
End If

If Intersect(Target, myRng) Is Nothing Then Exit Sub

If Target.HasFormula Then
MsgBox "Please don't type in this cell"
Application.EnableEvents = False
Me.Cells(Target.Row, "A").Select
Application.EnableEvents = True
End If

End Sub

If the user selects multiple cells, it'll reselect just the activecell. Then if
that cell has a formula, it'll select column A of the same row--to get them out
of it.

A bit more restrictive, though.

 

[ste mac]

Thanks to Frank, Bob, Paul B and Dave, all solutions were great, your
information makes it an easy move for me, thanks a lot for your time guys...

seeya ste