Display box

[greekboyuk]

Hi,

I would like to display a pop up box if value in column C is NO. Here is the explanation:

Column C2 formula is: =IF(A2="","",IF(OR(SUBSTITUTE(H2,CHAR(160),"")="CITY",B2="YES"),"YES","NO"))

In Column A is where I insert a postcode. As soon as the postcode e.g cell A2 then cells
C2,E2, F2, G2, H2 and I2 is displaying the address.

As soon as I inserted the postcode in cell A2 I would like:

1) If value in cell C2 = "No" pop up a message box sayin " The candidate is not eligible to apply unless they have got special abilities. Please ask candidate!!!! " and on the box have to choices one "Yes" and "No". If user select "Yes" then enter "Yes" in cell B2 and and jump to D2 so additional info can be entered manually. If user select "NO" in the pop up box then insert "No" in cell B2 , cut row 2 and paste it in Not eligible tab.from A2:I2. If next not eligible candidate paste it to A3:I3 etc.


2) If value in cell C2= "Yes" don't display pop up box and just move directly to cell D2.


I have attached a copy of my spreadsheet.


Many thanks in advance

greekboyuk

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[greekboyuk]

Hello royuk. is this a suitable title? if not please let me know?

Many thanks

greekboyuk

[davesexcel]

Your sample is 330kb zipped, obvious too large to be considered a sample.
Why not make it smaller so somebody would be glad to open it and help you.

[greekboyuk]

hello davesexcel,

My data is very small and I dont understand why it is so big. I uploaded an xlsx file which is approx 1mb but if I save it as excel 1997-03 it shows that it is 3MB which I dont understand why.

Hope that the new sample it helps

[davesexcel]

Try This, the msgbox code came right from the Excel Help File.

The code is the WorkSheet Module, Make a selection in Column A to Activate the code

[greekboyuk]

Try This, the msgbox code came right from the Excel Help File.

The code is the WorkSheet Module, Make a selection in Column A to Activate the code

Hello davesexcel thank you for your help, it looks good although it copies the data to "not elidgible" sheet and doesnt delete the row from "Recruitment sheet"
Also what do u mean by make a section in column A to activate the code?

Many thanks.

[davesexcel]

Use this code instead, it will delete the target row.

Please Login or Register  to view this content.

Did you notice your workbook had been reduced to 70kb ?

[greekboyuk]

hello davesexcel,

thank you for quick respond. still the rows have the "not eligible" criteria are only copied to the "not eligible" sheet. I still can see them in my recruitment sheet.
Btw how did u make the data only 70 kb?? thats magic

thanks

[davesexcel]

You need to replace the code in the worksheet module with the most recent code I gave you.
Right click on the sheet tab and select view code, delete the code there, then copy and paste the code from yesterday into that module.

On the original example you supplied, Hit Ctrl & End, this takes you to the end of the used range in your sheet, in this case it goes to row 65536. This usually happens when you format the entire sheet instead of just the used range.
Select the rows you are not using and goto Edit=>Clear=>All, then re-save the workbook.This will make the Workbook Smaller.

Find attached the Worksheet with the revised code.

[greekboyuk]

hello davesexcel,

thank you for the updated spreadsheet it works fine. I had pasted the code into the module and not the
workbook code. The only thing that is not working as I would have liked it is that when the postcode is in the "City" the cursor stays at the same cell and it doesn't jump in column D. It only jumps there once the pop up box is displayed and I select Yes but if the pop up box is not displayed (which mean that candidate meets criteria) I still would like that to jump in column D.

Many thanks