Code For pop Up Box Needed Please

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,786
Office Version
  1. 365
Platform
  1. Windows
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;"
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Try this:-
Code: 
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
 
Upvote 0
An error occured saying - Compile error - Ambiguous name detected: Worksheet_Change
 
Upvote 0
dazwm said:
An error occured saying - Compile error - Ambiguous name detected: Worksheet_Change
Click to expand...

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)'
 
Upvote 0
You can't have two:-"Private Sub Worksheet_Change(ByVal Target As Range)", in the same sheet.
Mick
 
Upvote 0
MickG said:
You can't have two:-"Private Sub Worksheet_Change(ByVal Target As Range)", in the same sheet.
Mick
Click to expand...

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
 
Upvote 0
Try this:-
Code: 
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
 
Upvote 0
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.
Rich (BB code): 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A

Forum statistics

Threads
1,224,521
Messages
6,179,287
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top