MsgBox for a range and not one cell change please:
 

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

MsgBox for a range and not one cell change please:
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany


ste mac wrote:
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

MsgBox for a range and not one cell change please:
 

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)

"Frank Kabel" wrote in message
...
Hi
change the line
If Target.Address = "$A$1" Then

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

--
Regards
Frank Kabel
Frankfurt, Germany


ste mac wrote:
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

MsgBox for a range and not one cell change please:
 

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" wrote in message
...
Hi
change the line
If Target.Address = "$A$1" Then

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

--
Regards
Frank Kabel
Frankfurt, Germany


ste mac wrote:
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

MsgBox for a range and not one cell change please:
 

Hi Bob
goot spot :-)

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


--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
also change

If ActiveCell.HasFormula Then

to

If Target.HasFormula Then



"Frank Kabel" wrote in message
...
Hi
change the line
If Target.Address = "$A$1" Then

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

--
Regards
Frank Kabel
Frankfurt, Germany


ste mac wrote:
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

MsgBox for a range and not one cell change please:
 

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 wrote:

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

--

Dave Peterson

MsgBox for a range and not one cell change please:
 

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