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;"

 

[Dazzawm]

Hi MickG. Could something else be added to the code. When I hover over or click on a name can it count up how many times HP is entered in their row and tell me in a little box or something? Thanks.

 

[Dazzawm]

Hi MickG. Could something else be added to the code. When I hover over or click on a name can it count up how many times HP is entered in their row and tell me in a little box or something? Thanks.

Or would it be easier if I added another sheet with all their names and each time I add a HP it increases and gives me a total in the column next to it.

 

[MickG]

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

 

[Dazzawm]

It wont work as I added it to the other code you gave me and it already has a 'Dim Rng As Range'

 

[MickG]

Just add the code as a totally new code underneath the "Selection Change Event code.
It it a seperate code that just works on the "Right Click" Event.
Mick

 

[Dazzawm]

Thanks, one other thing I just noticed that because there is a maximum amount of columns you are allowed the days will only go to October rather than December. What can I do?

 

[MickG]

If you know the last column the code should look in then change this:-

 Set Rng = Range(Range("C" & Target.Row), Cells(Target.Row, Columns.Count).End(xlToLeft))

To This:-
Change the 230 to your number of columns to look in.

 Set Rng = Range("C" & Target.Row).Resize(, 230)

In Fact I'm not sure why I did the code Like that, This is Better;-

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]
Cancel = True
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Columns("B:B")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
  [COLOR="Navy"]Set[/COLOR] Rng = Range("C" & Target.Row).Resize(, 230)
     num = Application.CountIf(Rng, "HP")
     MsgBox Target & " Has " & num & " HP'[COLOR="Green"][B]s"[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

The code isnt the problem, its the fact that I dont have enough columns. Is there nothing I can do to add more?

 

[MickG]

Perhaps you need to reorganise your sheet.
see here:-
http://news.office-watch.com/t/n.aspx?articleid=1408&zoneid=9

 

[Dazzawm]

Hi MickG. On this code you gave me is it possible to change it so that the pop up box appears only when somebody is already booked off that day. At the moment it pops up even if nobody is booked off already. Thanks.