trying to generate pop-up message when data entered into a cell

[theshooter]

Hi folks, (apologies for the long post, but the 'stickies' say give as much detail as possible, so... I did...)

I am working on a firewall configuration form. In that form (Sheet1), the person will enter in 1 or more server names and an IP address for each server, one server and IP per row. In the same workbook, I have another worksheet (Sheet2 of same workbook) that contains all the servers and IPs of a specific set of servers for lookup purposes.


If a person types a server name or IP address that is on that special list, I would like a pop-up window/warning to appear advising the person that the server or IP they just entered is associated with a special project.


I am able to use conditional formatting, but several users are not paying attention to the highlighted cell(s) resulting in problems later on with firewall entries. So, need a more "in your face" way to tell people they need to do something, thus the need for a pop-up message.


I found one similar posting that sounds like it has similar type of output, but I could not replicate it and make it work for me. I get a runtime error, so I probably misread/misunderstood the instructions. **I am not a VBA guy...** -- yet. [looks like I better learn a little as this kind of work keeps coming my way.]

[http://chandoo.org/forums/topic/compare-2-sheets-in-workbook-and-display-in-pop-up-msg]


I am using Excel 2007.


Sample of form:

[pre]

SourceHost	SourceIP
server1	10.3.214.7
server2	10.3.214.8
server3	10.3.214.9
server4	10.3.214.10
server5	10.3.214.11
server6	10.3.214.12
server7	10.3.214.13
server8	10.3.214.14
server9	10.3.214.15
server10	10.3.214.16
So, when they type in server1 (or an IP address) and press Enter, they should see a pop-up message/warning that says the entry they just made is associated with ProjectX. If the server name (or IP address) is not listed on Sheet2, do nothing.


On Sheet2, I have named ranges of:

servers

IPs


for the two columns that contain the two areas I want to lookup the information.


Sample of the Sheet2 data:

Project Name	App	HostName	Location	Status	Env-OS	IP Address
Project1	Application1	server1	Loc1	Production	Win2003	165.253.171.100
Project1	Application2	server2	Loc1	Production	Win2003	165.253.171.101
Project1	Application3	server3	Loc1	Production	Sol-9	165.253.171.105
Project2	Application4	server4	Loc2	DR	Win2008	165.253.171.13

[/pre]
NOTE: Sheet2 contains well over 200 entries.


Hopefully this contains the necessary info for the gurus/experts out there. I am fine with sticking VBA into my workbook, I just don't know it well enough to write it.


Thanks to all who've provided solutions and ideas. This site is pretty dang awesome!!


-- Jim

 

[SirJB7]

Hi, theshooter!


So you got the sticky-leading message, fine!


Give a look at this file:

http://dl.dropbox.com/u/60558749/trying%20to%20generate%20pop-up%20message%20when%20data%20entered%20into%20a%20cell%20%28for%20theshooter%20at%20chandoo.org%29.xlsm


It works triggering SelectionChange event, so either entering/updating data or just selecting it. I thought adding a hammer that comes out of the screen to hit fingers or head, but I'm running out of time.


Just advise if any issue.


Oh! One thing more: check your IP data type, it's both in text and number formatted format, so you can miss a critical hit if you don't standardize data between first and second sheets. Should I remember the 12 Codd rules? BTW, where are your IT developement guys??? They should have done this job!


Regards!

 

[theshooter]

SirJB7 - thanks! Ya, got the data all standardized as text to alleviate the number issues that can crop up.


I will take a look and give it a try.


Much appreciated!!

 

[theshooter]

As far as our dev guys - well, I cannot wait for them to get to this, so I had to ask the experts who I know can come up with several ways to tackle the issue and not take 6 weeks - YES, 6 weeks for them to even acknowledge I made a request!


Way beyond running "lean and mean" these folks are slammed against the wall.


SUPER BIG THANKS!!! Your code is clean and I can easily adapt as needed. I tried it really quick and it give exactly what I need!


BIG +++++ to you for your awesome help.

 

[SirJB7]

Hi, theshooter!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

PS: should I charge the bill to the IT Dev guys' cost center?

PS2: outsource your Office dev to chandoo's site (Chandoo! I want my 30%, minimum, eh!)

 

[theshooter]

SirJB7 - ya, great idea - at least we'd get some stuff in a timely fashion.


On a more serious note, it seems that the popup comes up even if there is not a matching server name. I am looking through the code and not sure where I would do the matching function, or how to do it.


How does one go about writing the code so darn fast? It amazes me!

 

[Montrey]

Like Luke, SirJB7 is an excel God. (especially at VBA)

 

[SirJB7]

Hi, theshooter!

Tomorrow I'll check it out. Remember that only by selecting a cell (or a contiguous group of cells) the search against critical table is performed.

Regards!

PS: it's simple, fire a couple of your IT devs and hire anybody of us... just one would be enough


@Montrey

Hi, PEEG!

Not at all, if Luke M is a god, I'd be still trying to reach a demigodness entity.

Regards!

 

[SirJB7]

Hi, theshooter!

Checked, worked fine.

But found an issue when updating, so added Change event too.

Please download it again from same link.

Regards!

PS: if still auto triggering, consider uploading the file, or send it by mail.

 

[theshooter]

Thanks - downloaded newest and it now gives pop-up warning when I enter only matching data. (server1, server2, server3 and server4) - if I type in server555 it does not appear! YAY!!


MUCH APPRECIATION AND GRATITUDE!!!

 

[SirJB7]

Hi, theshooter!

That wasn't what fixed, but if now OK, let it remains so. You're welcome.

Regards!