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]

Try this:-

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] Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B6"), Range("B" & rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Offset(, Target.Column - 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dn <> "" And Not Dn = Target [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] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

An error occured saying - Compile error - Ambiguous name detected: Worksheet_Change

 

[Dazzawm]

An error occured saying - Compile error - Ambiguous name detected: Worksheet_Change

I dont know if it makes a difference but I already have a module in this workbook called 'Private Sub Worksheet_Change(ByVal Target As Range)'

 

[MickG]

You can't have two:-"Private Sub Worksheet_Change(ByVal Target As Range)", in the same sheet.
Mick

 

[Dazzawm]

You can't have two:-"Private Sub Worksheet_Change(ByVal Target As Range)", in the same sheet.
Mick

So what can I do? Also I temporarily removed the other macro and this one did work but with problems. Everytime I entered something anywhere on the spreadsheet the pop up had MP and BH in it which are in B3 and B4

 

[MickG]

Try this:-

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] Msg [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B6"), Range("B" & rows.Count).End(xlUp))
[COLOR=navy]If[/COLOR] Not Intersect(Target, Rng.Offset(, 1).Resize(, 31)) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Rng.Offset(, Target.Column - 2)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Dn <> "" And Not Dn = Target [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] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Still doing it.

 

[MickG]

Place the last line below (In Red), in your code as shown.
This will show you the range that the code looks at to run the code.
After the Range address you will see the Target address.
If the target address is not within the Range address then the code will not run.

Dim Rng As Range, Dn As Range
Dim Msg As String
Set Rng = Range(Range("B6"), Range("B" & rows.Count).End(xlUp))
MsgBox Rng.Offset(, 1).Resize(, 31).Address & "//" & Target.Address

I have "C6: AG19" for the range
What do you get.???
.
As the Text "MP" & "BP" are in "B3 & B4 I'm not sure how your error is possible.
Mick

 

[Dazzawm]

The range I get is $C$6:$AG$39//$IK$33