Msgbox if you input a value smaller than another

**tsioumiou** · 09-02-2010, 03:45 PM

Hello,

Basically what i am trying to do is to pop up a Msgbox, if i put a value in a range of cells A1:A20 and this value is smaller than then corresponding number on B column, B1:B20.

For example if i put 10 in A12 and the value in B12 is 12, then the msg should pop up.

Something like

"the minimum size is "12".

Please enter a higher value"

Hope you can help

Thanks

**contaminated** · 09-02-2010, 04:00 PM

hI

Highlight range A1:A20
Go to Data, and press on Validation button in Data Tools group. Dialog Box will appear
In that dialog box, select Custom from dropdown
Type this formula there: =$B1<$A1
Press Ok.

NOTE: You can type your own error message in Alert tab,,,

**tsioumiou** · 09-02-2010, 04:42 PM

Thanks for the response,
the alert doesnt work for some reason.

And basically it would be much easier to do this with VBA with the command MsgBox,
I just need some help with the code

I was able to do it with 1 single cell but i am having a hard time with the range.

Thanks

**Paul** · 09-02-2010, 04:46 PM

Data Validation is actually the "easier" method.. but I think Contaminated's formula should be either

=$A1<$B1

or

=$B1>$A1

not

=$B1<$A1

If A is less than B (or B is greater than A), show the message. If A is greater than B (or B is less than A) don't show a pop-up.

**contaminated** · 09-02-2010, 04:57 PM

@ Paul
Please see screenshot

**Paul** · 09-02-2010, 05:00 PM

Yup.. you're right. I'm having a bad day with Data Validation apparently. For some reason I was thinking DV failed when the expression was True. ::smacking self::

**tsioumiou** · 09-02-2010, 05:14 PM

I managed to do it but i find it not so user friendly,

I attached a printscreen from what i did with VBA, as you can see is more user friendly.
I just have some problems , currently this is for the whole workbook (whereever i put a smaller value this msg pops up) and i cannot make it only for the range of cells i want.

I insist a bit on VBA for the reason that i want to lock it later (only VBA not anything else)so others can use without being able to make any changes it and plus its more professional. (for the reason i need it)

Hope the printscreen helps ( on the print sceen i am using column D and J)

Thanks again for the effort.

Thanks

**tsioumiou** · 09-02-2010, 05:15 PM

attachement. d is the far left column

**contaminated** · 09-02-2010, 05:17 PM

And basically it would be much easier to do this with VBA with the command MsgBox,

I can't really understand, why to use VBA, when there is built-in option?

With VBA, you will have too loop each cell in range. And the more the range the more the time it will take to loop. .....IMO.... (maybe there is another way in VBA w/o using loops). But Data Validation it takes seconds...

Can you please post a sample wb???

**tsioumiou** · 09-02-2010, 05:24 PM

Mainly but not only, for the fact the error msg is not so nice , ( with the retry and everything) it reminds me of old windows problems. If i needed the program for myself only i wouldnt mind.

I know there is a VBA for that, maybe its a bit more complicated than i first thought.

What do you mean sample wb?

**contaminated** · 09-02-2010, 05:31 PM

FYI, there is another Alert Style, like your MsgBox - Information. You just need to select thrid item of dropdown in Alert type. There are OK, Cancel and Help buttons.

Read this thread at leisure: http://www.excelforum.com/the-water-...o-do-this.html
Funny enough...

**tsioumiou** · 09-02-2010, 05:33 PM

Does reminds me of myself

**Leith Ross** · 09-02-2010, 05:42 PM

Hello tsioumiou,

This macro will display an alert for all worksheets when the value in "A1:A20" is less than the value in "B1:B20".

Please Login or Register  to view this content.

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

**tsioumiou** · 09-02-2010, 06:44 PM

thats great!

would you know how can i use it only for the specific worksheet?

**Leith Ross** · 09-02-2010, 08:28 PM

Hello ,

To make it sheet specific, you need to compare the desired sheet name to the current sheet in the event. Here is the modified code. Change the sheet name below in bold to what you will be using.

Please Login or Register  to view this content.

**tsioumiou** · 09-03-2010, 11:13 AM

Works perfectly.
One last thing, how can i change it to take the range from "D6:D20" and "J6:J20"
instead of A1:A20 and B1:20

I tried to do it myself but i couldnt

**Leith Ross** · 09-03-2010, 11:41 AM

Hello tsioumiou,

Here is the revised code for "D6:D20" and "J6:J20". Column "J" is offset from column "D" by 6 columns to right.

Please Login or Register  to view this content.

**tsioumiou** · 09-03-2010, 12:04 PM

I see. So now , you just showed me how to do that for any cell i want.

.

Very nice.

The only problem i am facing now is that even if i delete the value and the cell stays empty " ", the msg still pops up. Probably the system takes a blank cell as "0".

You know any quick way to fix that?

Thanks anyway for everything.

**Leith Ross** · 09-03-2010, 01:49 PM

Hello tsioumiou,

When you delete a value, the cell will be "empty" which we can test for.

Please Login or Register  to view this content.

**tsioumiou** · 09-03-2010, 02:54 PM

Correct once again.

You are the best

Msgbox if you input a value smaller than another

LinkBack

Thread Tools

Rate This Thread

Display

Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Re: Msgbox if you input a value smaller than another

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions