Hi! I was wondering if it is possible to write a macro so that if any of the
words "admin", "manager" or "co-ordinator" are entered into any of the cells
A1:B3, then a message box will pop up saying "Your current user status allows
editing". It would be great if it would work if any of those three words are
put in, but if it can only look out for one particular word, "admin" would be
enough.

Ideally (and this really would be perfect!) it would be great if the macro
could still look at cells A1:B3, but that it would have different messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and modify
content"
- If "co-ordinator" is entered it would display "Please contact extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

Try placing the following code in the "This workbook" workbook.sheetchange
event.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each c In Range("A1:B3")
Select Case c
Case "admin"
MsgBox (c)
Case "manager"
MsgBox (c)
Case "co-ordinator"
MsgBox (c)
End Select
Next c
End Sub

replace c with what ever message you want to display.

Jim F

"Neil Goldwasser" wrote:

Hi! I was wondering if it is possible to write a macro so that if any of the
words "admin", "manager" or "co-ordinator" are entered into any of the cells
A1:B3, then a message box will pop up saying "Your current user status allows
editing". It would be great if it would work if any of those three words are
put in, but if it can only look out for one particular word, "admin" would be
enough.

Ideally (and this really would be perfect!) it would be great if the macro
could still look at cells A1:B3, but that it would have different messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and modify
content"
- If "co-ordinator" is entered it would display "Please contact extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

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 have editing data and modify content"
Case "manager":
MsgBox "You have editing data and modify content"
Case "co-ordinator":
MsgBox "Please contact extension 1453 for more information"
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if any of
the
words "admin", "manager" or "co-ordinator" are entered into any of the
cells
A1:B3, then a message box will pop up saying "Your current user status
allows
editing". It would be great if it would work if any of those three words
are
put in, but if it can only look out for one particular word, "admin" would
be
enough.

Ideally (and this really would be perfect!) it would be great if the macro
could still look at cells A1:B3, but that it would have different messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and
modify
content"
- If "co-ordinator" is entered it would display "Please contact extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code similar to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if any of
the
words "admin", "manager" or "co-ordinator" are entered into any of the
cells
A1:B3, then a message box will pop up saying "Your current user status
allows
editing". It would be great if it would work if any of those three words
are
put in, but if it can only look out for one particular word, "admin" would
be
enough.

Ideally (and this really would be perfect!) it would be great if the macro
could still look at cells A1:B3, but that it would have different messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and
modify
content"
- If "co-ordinator" is entered it would display "Please contact extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

Many, many thanks to everyone that has helped me with this - you don't know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0, 1).Address(False,
False) & " has automatically changed to N/A because a " & "second support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask, but how
can I have more than one worksheet_change macro running in the same worksheet
(though functioning on different cells)? Ideally, both of these functions are
useful. I am hoping it is a case of just renaming the codes somehow to have
two different names, but when I had a go they both stopped working. Would
somebody please be able to advise me on how to alter the code to have more
than one macro running. Then if I am brave I might even try adding a third
bit later on to see if I've learned it - and won't need to pester everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code similar to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if any of
the
words "admin", "manager" or "co-ordinator" are entered into any of the
cells
A1:B3, then a message box will pop up saying "Your current user status
allows
editing". It would be great if it would work if any of those three words
are
put in, but if it can only look out for one particular word, "admin" would
be
enough.

Ideally (and this really would be perfect!) it would be great if the macro
could still look at cells A1:B3, but that it would have different messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and
modify
content"
- If "co-ordinator" is entered it would display "Please contact extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0, 1).Address(False,
False) & " has automatically changed to N/A because a " & "second support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask, but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these functions
are
useful. I am hoping it is a case of just renaming the codes somehow to
have
two different names, but when I had a go they both stopped working. Would
somebody please be able to advise me on how to alter the code to have more
than one macro running. Then if I am brave I might even try adding a third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if any
of
the
words "admin", "manager" or "co-ordinator" are entered into any of the
cells
A1:B3, then a message box will pop up saying "Your current user status
allows
editing". It would be great if it would work if any of those three
words
are
put in, but if it can only look out for one particular word, "admin"
would
be
enough.

Ideally (and this really would be perfect!) it would be great if the
macro
could still look at cells A1:B3, but that it would have different
messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and
modify
content"
- If "co-ordinator" is entered it would display "Please contact
extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

Hi Norman! Thanks for the explanation. I have now sandwiched both sets of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if I didn't
ask I would never know. Im learning more all the time now though.

My racing mind has now thought up a new question though. At the moment, one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a quick way
of adapting the code so that, for example, the message "Please contact
extension 1453 for more information" would show for more than one person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for more
information"
and so on...

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

Because this way is quicker I could include the different variations, to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it differently by
accident.

Again, thanks to all that have helped with my education, Neil Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0, 1).Address(False,
False) & " has automatically changed to N/A because a " & "second support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask, but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these functions
are
useful. I am hoping it is a case of just renaming the codes somehow to
have
two different names, but when I had a go they both stopped working. Would
somebody please be able to advise me on how to alter the code to have more
than one macro running. Then if I am brave I might even try adding a third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if any
of
the
words "admin", "manager" or "co-ordinator" are entered into any of the
cells
A1:B3, then a message box will pop up saying "Your current user status
allows
editing". It would be great if it would work if any of those three
words
are
put in, but if it can only look out for one particular word, "admin"
would
be
enough.

Ideally (and this really would be perfect!) it would be great if the
macro
could still look at cells A1:B3, but that it would have different
messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data and
modify
content"
- If "co-ordinator" is entered it would display "Please contact
extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both sets of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a quick
way
of adapting the code so that, for example, the message "Please contact
extension 1453 for more information" would show for more than one person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for more
information"
and so on...

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

Because this way is quicker I could include the different variations, to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it differently
by
accident.

Again, thanks to all that have helped with my education, Neil Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0, 1).Address(False,
False) & " has automatically changed to N/A because a " & "second
support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask, but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these
functions
are
useful. I am hoping it is a case of just renaming the codes somehow to
have
two different names, but when I had a go they both stopped working.
Would
somebody please be able to advise me on how to alter the code to have
more
than one macro running. Then if I am brave I might even try adding a
third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code
similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if
any
of
the
words "admin", "manager" or "co-ordinator" are entered into any of
the
cells
A1:B3, then a message box will pop up saying "Your current user
status
allows
editing". It would be great if it would work if any of those three
words
are
put in, but if it can only look out for one particular word, "admin"
would
be
enough.

Ideally (and this really would be perfect!) it would be great if the
macro
could still look at cells A1:B3, but that it would have different
messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data
and
modify
content"
- If "co-ordinator" is entered it would display "Please contact
extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual
Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both sets of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a quick
way
of adapting the code so that, for example, the message "Please contact
extension 1453 for more information" would show for more than one person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for more
information"
and so on...

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

Because this way is quicker I could include the different variations, to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it differently
by
accident.

Again, thanks to all that have helped with my education, Neil Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0, 1).Address(False,
False) & " has automatically changed to N/A because a " & "second
support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask, but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these
functions
are
useful. I am hoping it is a case of just renaming the codes somehow to
have
two different names, but when I had a go they both stopped working.
Would
somebody please be able to advise me on how to alter the code to have
more
than one macro running. Then if I am brave I might even try adding a
third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code
similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote in
message ...
Hi! I was wondering if it is possible to write a macro so that if
any
of
the
words "admin", "manager" or "co-ordinator" are entered into any of
the
cells
A1:B3, then a message box will pop up saying "Your current user
status
allows
editing". It would be great if it would work if any of those three
words
are
put in, but if it can only look out for one particular word, "admin"
would
be
enough.

Ideally (and this really would be perfect!) it would be great if the
macro
could still look at cells A1:B3, but that it would have different
messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data
and
modify
content"
- If "co-ordinator" is entered it would display "Please contact
extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual
Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

Neil,

Do you mean?

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

ws_exit:
Application.EnableEvents = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a
quick
way
of adapting the code so that, for example, the message "Please contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for more
information"
and so on...

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

Because this way is quicker I could include the different variations,
to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a
given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,
1).Address(False,
False) & " has automatically changed to N/A because a " & "second
support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask,
but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these
functions
are
useful. I am hoping it is a case of just renaming the codes somehow
to
have
two different names, but when I had a go they both stopped working.
Would
somebody please be able to advise me on how to alter the code to
have
more
than one macro running. Then if I am brave I might even try adding
a
third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code
similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote
in
message ...
Hi! I was wondering if it is possible to write a macro so that
if
any
of
the
words "admin", "manager" or "co-ordinator" are entered into any
of
the
cells
A1:B3, then a message box will pop up saying "Your current user
status
allows
editing". It would be great if it would work if any of those
three
words
are
put in, but if it can only look out for one particular word,
"admin"
would
be
enough.

Ideally (and this really would be perfect!) it would be great if
the
macro
could still look at cells A1:B3, but that it would have
different
messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data
only"
- If "manager" is entered it would display "You have editing
data
and
modify
content"
- If "co-ordinator" is entered it would display "Please contact
extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a
Visual
Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a
quick
way
of adapting the code so that, for example, the message "Please contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for more
information"
and so on...

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

Because this way is quicker I could include the different variations,
to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a
given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,
1).Address(False,
False) & " has automatically changed to N/A because a " & "second
support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask,
but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these
functions
are
useful. I am hoping it is a case of just renaming the codes somehow
to
have
two different names, but when I had a go they both stopped working.
Would
somebody please be able to advise me on how to alter the code to
have
more
than one macro running. Then if I am brave I might even try adding a
third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code
similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote
in
message ...
Hi! I was wondering if it is possible to write a macro so that if
any
of
the
words "admin", "manager" or "co-ordinator" are entered into any
of
the
cells
A1:B3, then a message box will pop up saying "Your current user
status
allows
editing". It would be great if it would work if any of those
three
words
are
put in, but if it can only look out for one particular word,
"admin"
would
be
enough.

Ideally (and this really would be perfect!) it would be great if
the
macro
could still look at cells A1:B3, but that it would have different
messages
depending on which key-word is entered. e.g.
- If "admin" is entered it would display "You may edit data only"
- If "manager" is entered it would display "You have editing data
and
modify
content"
- If "co-ordinator" is entered it would display "Please contact
extension
1453 for more information"
Can anybody think of a way of doing this? (I am a bit of a Visual
Basic
novice, but can follow instructions!)

Many thanks in advance, Neil Goldwasser

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a
quick
way
of adapting the code so that, for example, the message "Please contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for more
information"
and so on...

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

Because this way is quicker I could include the different variations,
to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a
given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote in
message ...
Many, many thanks to everyone that has helped me with this - you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,
1).Address(False,
False) & " has automatically changed to N/A because a " & "second
support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro, I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could ask,
but
how
can I have more than one worksheet_change macro running in the same
worksheet
(though functioning on different cells)? Ideally, both of these
functions
are
useful. I am hoping it is a case of just renaming the codes somehow
to
have
two different names, but when I had a go they both stopped working.
Would
somebody please be able to advise me on how to alter the code to
have
more
than one macro running. Then if I am brave I might even try adding a
third
bit later on to see if I've learned it - and won't need to pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in code
similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser" wrote
in
message ...
Hi! I was wondering if it is possible to write a macro so that if
any
of
the
words "admin", "manager" or "co-ordinator" are entered into any
of
the
cells
A1:B3, then a message box will pop up saying "Your current user
status

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if
I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

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

Because this way is quicker I could include the different
variations,
to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a
given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one
single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote
in
message ...
Many, many thanks to everyone that has helped me with this - you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had
already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,
1).Address(False,
False) & " has automatically changed to N/A because a " & "second
support
plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

(And again, many thanks to JE McGimpsey, and
Bob
Phillips
for helping me construct that one!)

The problem is that when I added a second worksheet change macro,
I
received
an error message saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I'm sure it is a question that only a novice like myself could
ask,
but
how
can I have more than one worksheet_change macro running in the
same
worksheet
(though functioning on different cells)? Ideally, both of these
functions
are
useful. I am hoping it is a case of just renaming the codes
somehow
to
have
two different names, but when I had a go they both stopped
working.
Would
somebody please be able to advise me on how to alter the code to
have
more
than one macro running. Then if I am brave I might even try
adding a
third
bit later on to see if I've learned it - and won't need to
pester
everyone
:-)

Many thanks, Neil Goldwasser


"Tom Ogilvy" wrote:

Assumes A1:B3 does not have merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg as String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B3")) Is Nothing Then
msg = ""
Select Case LCase(Target)
Case "admin"
msg = "You may edit data only"
Case "manager"
msg = "You have editing data and modify content"
Case "co-ordinator"
msg = "Please contact extension 1453 for more
information"
End Select
If msg < "" Then
MsgBox msg
End If
End If
End Sub

Right click on the sheet tab and select view code. Paste in
code
similar
to
the above.

--
Regards,
Tom Ogilvy

"Neil Goldwasser"
wrote
in
message
...
Hi! I was wondering if it is possible to write a macro so that
if
any
of
the
words "admin", "manager" or "co-ordinator" are entered into
any
of
the
cells
A1:B3, then a message box will pop up saying "Your current
user
status

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and if
I
didn't
ask I would never know. I'm learning more all the time now though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three different
"roles". If new roles, or new people came into the team, is there a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

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

Because this way is quicker I could include the different
variations,
to
ensure that each role is picked up, e.g. co-ordinator, Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure behind a
given
worksheet.

However, it is possible to include alternative operations acting on
different ranges within the one procedure. So, the solution to your
problem
is to combine the code from each of your two versions into one
single
procedure.

---
Regards,
Norman



"Neil Goldwasser" wrote
in
message ...
Many, many thanks to everyone that has helped me with this - you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had
already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,

Hi Neil,

I put the code in the module behind Sheet1; I selected Sheet2 and defined
the name ModificationRights to refer to cells A1:A10 (Insert | Name |
Define ...); I populated the newly named range on Sheet2 with some first
name (Anne, Ben, Carol...).
Returning to Sheet1, entering any of the names from the Sheet2 named range
caused a "Your Group Message!" msgbox to appear.

Assuming that the name is a workbook level name, it should not matter where
the named range is within the workbook.

---
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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and
if
I
didn't
ask I would never know. I'm learning more all the time now
though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three
different
"roles". If new roles, or new people came into the team, is there
a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

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

Because this way is quicker I could include the different
variations,
to
ensure that each role is picked up, e.g. co-ordinator,
Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure
behind a
given
worksheet.

However, it is possible to include alternative operations acting
on
different ranges within the one procedure. So, the solution to
your
problem
is to combine the code from each of your two versions into one
single
procedure.

---
Regards,
Norman



"Neil Goldwasser"
wrote
in
message
...
Many, many thanks to everyone that has helped me with this -
you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had
already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target,
Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and
if
I
didn't
ask I would never know. I'm learning more all the time now
though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three
different
"roles". If new roles, or new people came into the team, is there
a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

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

Because this way is quicker I could include the different
variations,
to
ensure that each role is picked up, e.g. co-ordinator,
Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure
behind a
given
worksheet.

However, it is possible to include alternative operations acting
on
different ranges within the one procedure. So, the solution to
your
problem
is to combine the code from each of your two versions into one
single
procedure.

---
Regards,
Norman



"Neil Goldwasser"
wrote
in
message
...
Many, many thanks to everyone that has helped me with this -
you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had
already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target,
Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,

Hi Neil,

Typo warning!

Change:

Sheets(2).Range(arr(i)), 0)) Then
to:
NamesSheet.Range(arr(i)), 0)) Then

---
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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched
both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and
if
I
didn't
ask I would never know. I'm learning more all the time now
though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three
different
"roles". If new roles, or new people came into the team, is
there a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than
one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

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

Because this way is quicker I could include the different
variations,
to
ensure that each role is picked up, e.g. co-ordinator,
Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure
behind a
given
worksheet.

However, it is possible to include alternative operations
acting on
different ranges within the one procedure. So, the solution to
your
problem
is to combine the code from each of your two versions into one
single
procedure.

---
Regards,
Norman



"Neil Goldwasser"
wrote
in
message
...
Many, many thanks to everyone that has helped me with this -
you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had
already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target,
Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,

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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched
both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and
if
I
didn't
ask I would never know. I'm learning more all the time now
though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three
different
"roles". If new roles, or new people came into the team, is
there a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than
one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

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

Because this way is quicker I could include the different
variations,
to
ensure that each role is picked up, e.g. co-ordinator,
Co-ordinator,
coordinator, Coordinator, and so on, in case they spelled it
differently
by
accident.

Again, thanks to all that have helped with my education, Neil
Goldwasser




"Norman Jones" wrote:

Hi Neil,

It is only possible to have one Worksheet_Change procedure
behind a
given
worksheet.

However, it is possible to include alternative operations
acting on
different ranges within the one procedure. So, the solution to
your
problem
is to combine the code from each of your two versions into one
single
procedure.

---
Regards,
Norman



"Neil Goldwasser"
wrote
in
message
...
Many, many thanks to everyone that has helped me with this -
you
don't
know
how much I appreciate it!
I have one more novice question though, if I may ask. I had
already
inserted
a Worksheet_Change code for that worksheet, as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target,
Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
MsgBox "Cell " & rCell.Offset(0,

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 (Hertford
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

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

It works! It works! It works!
Thanks ever so much for that Norman, you've made my day! I was even brave
enough to try adding a fourth group and corresponding message to see if I was
able to add subsequent groups, and to my surprise I could! Just goes to show
what you can do when you have somebody much cleverer to do it for you!! :-)

I owe you one, this will really help our new tracking system! (in case I
haven't already mentioned the overall aim of my project is to design a brand
new tracking form to effectively monitor the support provided to the students
we have at the college with learning difficulties. It will now mean that
students won't "slip through the net" and miss out on the support that they
are entitled to. So you have been helping a good cause and I really
appreciate it!)

Thanks for the warning about spammers by the way, I'll bear that in mind.

Okay, I think case closed on that matter now. Thanks to everyone, Neil
Goldwasser



"Norman Jones" wrote:

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

"Norman Jones" wrote:

Hi Neil,

I put the code in the module behind Sheet1; I selected Sheet2 and defined
the name ModificationRights to refer to cells A1:A10 (Insert | Name |
Define ...); I populated the newly named range on Sheet2 with some first
name (Anne, Ben, Carol...).
Returning to Sheet1, entering any of the names from the Sheet2 named range
caused a "Your Group Message!" msgbox to appear.

Assuming that the name is a workbook level name, it should not matter where
the named range is within the workbook.

---
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
message ...
Hi Norman! Thanks for the explanation. I have now sandwiched both
sets
of
code between the
"Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the
"End Sub" line
and it is working really well.

Sorry if it was a really simple question, but I am a novice, and
if
I
didn't
ask I would never know. I'm learning more all the time now
though.

My racing mind has now thought up a new question though. At the
moment,
one
of my sections of code is:
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 "manager":
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

This allows three different messages for each of the three
different
"roles". If new roles, or new people came into the team, is there
a
quick
way
of adapting the code so that, for example, the message "Please
contact
extension 1453 for more information" would show for more than one
person?

I realise that I could keep adding cases, e.g.
Case "admin":
MsgBox "You have edit data only"
Case "manager":
MsgBox "You may edit data and modify content"
Case "co-ordinator":
MsgBox "Please contact Neil on extension 1453 for
more
information"
Case "IT team"
MsgBox "Please contact Neil on extension 1453 for
more
information"
and so on...

but is there a quicker way, something along the lines of
Case "co-ordinator" "IT team":


Can you change this to happen when any cell is selected instead of typing
admin or anything else into a cell?