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