Help with existing pop up code

[Dazzawm]

I was given the codes within this thread http://www.mrexcel.com/forum/showthread.php?t=554868

Problem now is I have changed it from horizontal to vertical, so the names are going across the top and the dates down in column B. I need a pop up so that if someone has already an 'HP' in that date/row it will give a pop up saying 'such and such is already booked of that day' by looking at the name in row 3.

The names are ranging from C3:CB3 and the dates range from B4:B316

Thanks

 

[Dazzawm]

That seems to be it thank you so much for all your time. One question, should I need to insert a column within a range for a new member of staff for example how will it affect the code?

 

[MickG]

You should have no problems inserting new names, as long as you keep that gap in row (3) between each group.
Mick

 

[Dazzawm]

Sorry thought of another thing! If I wanted it to count the RDs as well as the HPs how would I put them in the code? Like this "HP, RD"?

 

[MickG]

Try this:-
This gives a count for all "Initials".
Dete from code array if not wanted in result.

[COLOR="Navy"]Sub[/COLOR] MG16Jun06
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeRightClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rows("3:3")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
Cancel = True
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(3, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
Txt = Array("HP", "MP", "SU", "BH", "HU", "RD")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Txt)
        [COLOR="Navy"]With[/COLOR] Application
            [COLOR="Navy"]If[/COLOR] .CountIf(Rng, Txt(n)) > 0 [COLOR="Navy"]Then[/COLOR]
                msg = msg & .CountIf(Rng, Txt(n)) & Space(2) & """" & Txt(n) & """" & Chr(10)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Next[/COLOR] n
MsgBox Target & " has :- " & Chr(10) & msg
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

That works great but I have just noticed something in the other code you gave me. When I enter in a cell and it says such and such are off today it is counting anything that is in other cells when it should only be the HP's.

 

[MickG]

In the lines below change the word "Target" (in your code) for Dn as shown in Red below.
NB:- This code counts all the "HP"'s except the one you've just Typed in. If you want to count all of them , remove the Code in Blue.

Depot = IIf(nDn(1).Column = 3, nDn(1).Offset(-Target.Row + 1), nDn(1).Offset(-Target.Row + 1, -1))
        For Each Dn In nDn
                If Dn = "HP" And Dn <> "" And Not Dn.Address = Target.Address Then
                    msg = msg & Cells(3, Dn.Column) & Chr(10)
                 nFd = True
                End If
            Next Dn

 

[Dazzawm]

Thanks one more thing what If I want it to count the RDs as well as the HPs in the row?

 

[MickG]

Change lines as below:-

 For Each Dn In nDn
                If Not Dn.Address = Target.Address Then
                    If Dn = "HP" Or Dn = "RD" And Dn <> "" Then
                        msg = msg & Cells(3, Dn.Column) & Chr(10)
                        nFd = True
                    End If
                End If
            Next Dn

 

[Dazzawm]

Thanks it counts everything in the row when I enter HP inc RDs but when I enter a RD the pop up doesnt happen

 

[MickG]

Change this Line (In red)
(I'm starting to forget what day it is !!!)

If nFd And Target = "HP" Or Target = "RD" Then
    MsgBox "The following Staff are off today " & Chr(10) & Chr(10) & nMsg & Chr(10) & "Press ""OK"" to continue !!"
End If

Mick