Hi Neil,

I Have sent a simple demo book.

For future reference, you would be well advised to disguise your email
address: spammers voraciously pounce on any unwarily published email
addresses.

Looking at the posts of regular contributors, you will see examples of such
disguise. My own paranoia is such that I only post a (disguised) address if
explicitly inviting an email.

BTW as I am from St Albans, you are a mere stone's throw away!

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Hi again Norman! I really appreciate all of this. Unfortunately though I
just
don't seem to be able to get it right. No doubt it'll be another one of my
embarassingly silly mistakes, but it seems as though Excel has got me
beaten.

I don't suppose you would have made a "tester" workbook that you could
e-mail me instead do you. Don't worry if not, or if sending files isn't a
done thing in discussion groups (I'm relatively new to the whole community
thing and I'm still not sure of the right etiquette!), but if you could
that
would be amazing.

My e-mail address if you can do it is XXXXXXXXXXXXXXX!!!
Regional College being the base of the learning support department that is
currently benfiting from all your hard work!)

Either way, many thanks for all your help. I'll keep trying!
Neil Goldwasser


"Norman Jones" wrote:

Hi Neil,

Just to add, each of the named ranges corresponding to the name strings
in
arr, need to exist. Simply define them via the Insert | Names menu prior
to
running the code.

The sequence of message strings in ArrMsg must match the sequence of
named
ranges, else the wrong message will appear!

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Neil,

I was also wondering if there is a simple way of adding to the code so
that
I could have several groups, each with their own message.
E.g. "ModificationRights" would show "You may edit data and modify
content"
"DataEditingRights" would show "You may edit data only"
"ReadOnlyRights" would show "You only have read-only access and may
not
edit
data"


Sorry if I'm being a pain, but this will pay off big time in the
future

Try:

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)

Dim arr As Variant
Dim ArrMsg As Variant
Dim i As Long
Dim NamesSheet As Worksheet

Set NamesSheet = Sheets("Sheet2") '<<==== Change

ArrMsg = Array("You may edit data only", _
"Check Your modifcation rights!", _
"You only have read-only access and may not edit")

arr = Array("DataEditingRights", _
"ModificationRights", _
"ReadOnlyRights")

On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then
For i = LBound(arr) To UBound(arr)
If Not IsError(Application.Match(Target.Value, _
Sheets(2).Range(arr(i)), 0)) Then

MsgBox ArrMsg(i)
Exit For
End If
Next
End If

ws_exit:
Application.EnableEvents = True

End Sub
''<<'=============================


---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Thanks Norman! It's working now!

One quick question though - I have discovered that it will only work
for
me
if that set of named cells is on the same sheet, i.e. the values that
come
under the "ModificationRights" name are also on the sheet with the
macro
in
it. I had previously been putting all the named cells that I will use
in
the
drop-down lists etc... in a different sheet, to keep things "tidy". Is
there
a way that it will let me hide the names in a different sheet?

I was also wondering if there is a simple way of adding to the code so
that
I could have several groups, each with their own message.
E.g. "ModificationRights" would show "You may edit data and modify
content"
"DataEditingRights" would show "You may edit data only"
"ReadOnlyRights" would show "You only have read-only access and may
not
edit
data"


Sorry if I'm being a pain, but this will pay off big time in the
future!
Many thanks, Neil Goldwasser



"Norman Jones" wrote:

Hi Neil,

I inadvertently used the Worksheet_SelectionChange event.

Change this to:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then
Select Case Target.Value
Case "admin":
MsgBox "You may edit data only"
Case "ModificationRights":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension " & _
"1453 for more Information "
Case Else
If Not IsError(Application.Match(Target.Value, _
Range("ModificationRights"), 0)) Then
MsgBox "Your Group Message!"
End If
End Select
End If

ws_exit:
Application.EnableEvents = True

End Sub

If I then defined (via Insert | Names) a range (say, J:10) as
ModificationRights, then typing any entry from the ModificationRights
range
brought up the message: "Your Group Message!" .

My sub was intended purely for demo purposes and it is unlikely that
you
would also want additionally to test for the "ModificationRights"
string
(see the 2nd Case)

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Hi again! Thankyou both for your suggestions. Unfortunately though
I
cannot
get either method to work. I grouped together a list of usernames
under
the
name "ModificationRights", one of those being "ngoldwasser".
However,
when
I
typed "ngoldwasser" into one of the cells A1:B3, it didn't work as
planned.

I'm sure it is something I am doing, but here is what happens:

With Bob's method I receive a syntax error, and the "select
case..."
and
"case not intersect..." lines appear in red. I put this code in the
worksheet_change event.

With Norman's method nothing happens if I type in ngoldwasser. I
tried
typing in "admin" as well, and no message box popped up until I
clicked
bak
on the cell a second time. I had put this code in as a separate
worksheet_selection change event.

Can anyone tell me what I'm doing wrong please?
Many thanks, Neil Goldwasser



"Norman Jones" wrote:

Hi Neil,

Or did you mean something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then
Select Case Target.Value
Case "admin":
MsgBox "You may edit data only"
Case "ModificationRights":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension " & _
"1453 for more Information "
Case Else
If Not IsError(Application.Match(Target.Value, _
Range("ModificationRights"), 0)) Then
MsgBox "Your Group Message!"
End If
End Select
End If

ws_exit:
Application.EnableEvents = True

End Sub


---
Regards,
Norman



"Neil Goldwasser" wrote
in
message ...
Thanks Norman, I knew it would be something embarrassing! I'd
tried
everything else, but not that one. Cheers.

Would there also be a way of naming cells, so that I could group
together
all the users who, for example, I will allow modification
rights,
as
one
name, e.g. "ModificationRights"?

I tried the following, but it is looking for
"ModificationRights"
as
the
value, and I don't know how to alter the code to allow the range
of
values
that come under the name "ModificationRights" instead:

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then
Select Case Target.Value
Case "admin":
MsgBox "You may edit data only"
Case "ModificationRights":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
End Select
End If

ws_exit:
Application.EnableEvents = True


Any chance that you might be able to solve this one as well
please?

Thankyou, Neil Goldwasser



"Norman Jones" wrote:

Hi Neil,

but is there a quicker way, something along the lines of
Case "co-ordinator" "IT team":
MsgBox "Please contact extension 1453 for more
information"

Indeed there is. and you are only a whisker (or a comma!)
away.

Simply separate the case items with a comma:

Case "Manager1", " Manager2", "Mamager3", ..."Manager20"
MsgBox "Please contact extension 1453 for more
information"


---
Regards,
Norman



"Neil Goldwasser"
wrote
in