Code For pop Up Box Needed Please

[Dazzawm]

I need a code that will give me a warning box when anything is entered in a column when something is already in that column. If you look at the example below 'HP' is entered in F9 so if I made another entry in say F16 it would look at the name already entered in column B and a pop up would say 'James Cox is already booked off that day press ok to continue'. The example is just a small piece of the file and it would stretch down several 1000 rows.

I would need it to do the same if there are several entries in the column already too i.e 'Tom Smith, Lee Westwood, Curtis Stigers are already booked off that day press ok to continue' etc etc...

Is this possible? I hope so!!! Thanks for your help.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:85px;" /><col style="width:122px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:80px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">HP</td><td style="background-color:#0000ff; font-weight:bold; text-align:left; ">RD</td><td style="background-color:#99cc00; font-weight:bold; text-align:left; ">January</td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#99cc00; font-weight:bold; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#00ff00; font-weight:bold; text-align:left; ">SU</td><td style="background-color:#ff00ff; font-weight:bold; text-align:left; ">MP</td><td style="background-color:#ffff00; text-align:left; ">02/01/2012</td><td style="background-color:#ffff00; text-align:left; ">03/01/2012</td><td style="background-color:#ffff00; text-align:left; ">04/01/2012</td><td style="background-color:#ffff00; text-align:left; ">05/01/2012</td><td style="background-color:#ffff00; text-align:left; ">06/01/2012</td><td style="background-color:#808080; text-align:left; ">07/01/2012</td><td style="background-color:#ffff00; text-align:left; ">09/01/2012</td><td style="background-color:#ffff00; text-align:left; ">10/01/2012</td><td style="background-color:#ffff00; text-align:left; ">11/01/2012</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ff0000; font-weight:bold; text-align:left; ">SP</td><td style="background-color:#00ffff; font-weight:bold; text-align:left; ">BH</td><td style="background-color:#ffff00; text-align:left; ">Monday</td><td style="background-color:#ffff00; text-align:left; ">Tuesday</td><td style="background-color:#ffff00; text-align:left; ">Wednesday</td><td style="background-color:#ffff00; text-align:left; ">Thursday</td><td style="background-color:#ffff00; text-align:left; ">Friday</td><td style="background-color:#808080; text-align:left; ">Saturday</td><td style="background-color:#ffff00; text-align:left; ">Monday</td><td style="background-color:#ffff00; text-align:left; ">Tuesday</td><td style="background-color:#ffff00; text-align:left; ">Wednesday</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Role</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Name</td><td style="text-align:left; ">1</td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td style="text-align:left; ">2</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff00; text-align:left; ">Manager</td><td style="background-color:#ffff00; text-align:left; ">Tom Smith</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffff00; text-align:left; ">*** Manager</td><td style="background-color:#ffff00; text-align:left; ">Chris Jones</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff00; text-align:left; ">Warehouse</td><td style="background-color:#ffff00; text-align:left; ">Chris Brown</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff00; text-align:left; ">Warehouse</td><td style="background-color:#ffff00; text-align:left; ">James Cox</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; text-align:left; ">HP</td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Curtis Stigers</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Alan Furlonger</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">John Harris</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Leslie Turner</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Michael Flower</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Mike Dobson</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Lee Westwood</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ffff00; text-align:left; ">Driver</td><td style="background-color:#ffff00; text-align:left; ">Steve Ellis</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ffff00; text-align:left; ">Biker</td><td style="background-color:#ffff00; text-align:left; ">Diego Ardiles</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#808080; "> </td><td > </td><td > </td><td > </td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;"

 

[MickG]

Do you mean the first code or second, and what is it that tells you when a person is booked on or off for that day ???

 

[Dazzawm]

Try this:-
I was not aware you wanted this to work over a number of similar ranges (Depots)
This should work over each Seperate range of cells within a column (Ranges/depots) where seperation of ranges is Identified by at least one blank row.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nDn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Msg [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B6"), Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Rng = Rng.SpecialCells(xlCellTypeConstants)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Areas
        [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] nRng = Dn
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dn.Offset(4).Resize(Dn.Count - 4))
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] nDn [COLOR=navy]In[/COLOR] nRng.Areas
[COLOR=navy]If[/COLOR] Not Intersect(Target, nDn.Offset(, 1).Resize(, 31)) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] nDn = nDn.Offset(, Target.Column - 2)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] nDn
        [COLOR=navy]If[/COLOR] Dn <> "" And Not Dn.Address = Target.Address [COLOR=navy]Then[/COLOR]
            Msg = Msg & Range("B" & Dn.Row) & Chr(10)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]If[/COLOR] Msg <> "" [COLOR=navy]Then[/COLOR]
    MsgBox "The following Staff are off today " & Chr(10) & Msg & Chr(10) & "Press ""OK"" to continue !!"
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] nDn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Its for this code, all works great its just I dont want the pop up if nobody is off. Thanks.

 

[MickG]

Try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range
Dim nDn As Range
Dim Msg As String
Dim nMsg As String
Dim nRng As Range
Dim Depot As String
Dim Fd As Boolean
Dim nFd As Boolean
Set Rng = Range(Range("B6"), Range("B" & Rows.Count).End(xlUp))
Set Rng = Rng.SpecialCells(xlCellTypeConstants)
    For Each Dn In Rng.Areas
        If nRng Is Nothing Then
            Set nRng = Dn
        Else
            Set nRng = Union(nRng, Dn.Offset(4).Resize(Dn.Count - 4))
        End If
Next Dn
For Each nDn In nRng.Areas
    If Not Intersect(Target, nDn.Offset(, 1).Resize(, Columns.Count - 2)) Is Nothing Then Fd = True
Next nDn
If Fd Then
    For Each nDn In nRng.Areas
        Depot = nDn.Offset(-5, -1).Resize(1)
        Set nDn = nDn.Offset(, Target.Column - 2)
            For Each Dn In nDn
                If Target = "HP" And Dn <> "" And Not Dn.Address = Target.Address Then
                    Msg = Msg & Range("B" & Dn.Row) & Chr(10)
                 nFd = True
                End If
            Next Dn
                nMsg = nMsg & "Depot " & """" & Depot & """" & Chr(10) & Msg & Chr(10)
                Msg = ""
    Next nDn
End If
If nFd And Target = "HP" Then
    MsgBox "The following Staff are off today " & Chr(10) & Chr(10) & nMsg & Chr(10) & "Press ""OK"" to continue !!"
End If
End sub

 

[Dazzawm]

That works, thanks for all your help Mick.

 

[Dazzawm]

Try this:-
When you Right click on any cell in column "B" it will Return the Name Clicked and the Number of "HP's" for that row

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Long
Cancel = True
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Dim Rng As Range, Dn As Range
 Set Rng = Range(Range("C" & Target.Row), Cells(Target.Row, Columns.Count).End(xlToLeft))
    For Each Dn In Rng
        If Dn = "HP" Then c = c + 1
    Next Dn
End If
    MsgBox Target & " Has " & c & " HP's"
End Sub

Mick

Hi Mick this works great but all the right click options are disabled when I right click elsewhere on the sheet. Can this be fixed so all the normal options are there like , copy, paste etc apart from when I click on a name in column B. Thanks.

 

[Dazzawm]

Hi MickG could you help further with this please? http://www.mrexcel.com/forum/showthread.php?t=556950