Linking a Message Box to a cell value

  • Thread starter Thread starter RamblinWreck
  • Start date Start date
R

RamblinWreck

Okay, this is probably the worst way to do things, but I want to link
cell value to a message box. I am basically going to use the messag
box as a method of data validation. If the data value of the cel
exceeds a certain data range, then the message box should pop up an
alert the user that his input data is "out-of-range"

I can't really use the data validation approach. I have a drop dow
menu next to the input data cell to allow for different input units.
This drop down menu is used along with conversion factors to a specifi
unit that I want to use in my calculations.

The attached spreadsheet illustrates my basic dilema. On sheet 1, th
user inputs his length dimension and then selects his units.

On sheet 2, the user's length is converted to an equivalent value i
inches. When the value of cell B7 on sheet2 is not between 5 and 20,
would like a message box to pop up and alert the user to this fact.

The user will never, ever see sheet 2. He will always be on sheet 1
sheet 1 functions as the GUI for the input data and the results.
Sheet2 is where all the caclulations are done.

I realize that my problem is pretty simplistic, but I am still gettin
use to some of the more "powerful" aspects of Excel.

I am assuming that the best way to handle this would be a VBA macro
but I am a little bit at a loss as to how to proceed. First, I can'
figure out how to have the macro always checking the cell on sheet
while the user is actually staying put on sheet 1.

I am middle aged engineer, and I am very comfortable with Fortan, but
can pretty readily figure out C, C++, and Java programs. My point her
is that if somebody wrote a simple macro to handle this problem, I a
confident that I could figure it out (provided the object oriente
stuff doesn't get too complicated).

Thanks for taking the time to read this !

Ton
 
Hi
could be done with 'Data - Validation' without using VBA. Try the
following:
- first define a name for cell B7 on your second sheet:
-> select this cell
-> goto 'Insert - Name - Define'
-> e.g. use the name test_boundary
- now select the input cell on your first sheet
- goto 'Data - Validation' and choose custom
- enter the following formula:
=(test_boundary>=5)*(test_boundary<=20)
 
Frank said:
Hi
could be done with 'Data - Validation' without using VBA. Try the
following:
- first define a name for cell B7 on your second sheet:
-> select this cell
-> goto 'Insert - Name - Define'
-> e.g. use the name test_boundary
- now select the input cell on your first sheet
- goto 'Data - Validation' and choose custom
- enter the following formula:
=(test_boundary>=5)*(test_boundary<=20)


--
Regards
Frank Kabel
Frankfurt, Germany

Frank

Thanks for the quick reply. Your approach gets me most of the wa
there, but not quite all the way. First, the data validation onl
works on the number entered, not when the units are changed.

For example, if I enter in 10 in cell F11 on sheet 1 (user inpu
cell), and the units are set in inches then, I will not get an erro
prompt. - and this is as it should be. However, if I now leave tha
10 and change the units value to feet, I will also not get an erro
prompt. This latter case is a problem as I should get an erro
prompt.

If I start out with the units set in feet, and then enter in 10,
will get the correct error prompt.

Updated SS is attached.

Thanks again !

Ton
Click to expand...

Attachment filename: inchconversionspreadsheet-2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55000
Click to expand...
 
Hi
yes this is one of the restrictions in data_validation. If you want to
prevent this you have to use VBA. Using the worksheet_change event. But
before doing this I would consider creating a userform and doing the
checking within this userform

--
Regards
Frank Kabel
Frankfurt, Germany

Frank said:
Hi
could be done with 'Data - Validation' without using VBA. Try the
following:
- first define a name for cell B7 on your second sheet:
-> select this cell
-> goto 'Insert - Name - Define'
-> e.g. use the name test_boundary
- now select the input cell on your first sheet
- goto 'Data - Validation' and choose custom
- enter the following formula:
=(test_boundary>=5)*(test_boundary<=20)


--
Regards
Frank Kabel
Frankfurt, Germany

Frank

Thanks for the quick reply. Your approach gets me most of the way
there, but not quite all the way. First, the data validation only
works on the number entered, not when the units are changed.

For example, if I enter in 10 in cell F11 on sheet 1 (user input
cell), and the units are set in inches then, I will not get an error
prompt. - and this is as it should be. However, if I now leave that
10 and change the units value to feet, I will also not get an error
prompt. This latter case is a problem as I should get an error
prompt.

If I start out with the units set in feet, and then enter in 10, I
will get the correct error prompt.

Updated SS is attached.

Thanks again !

Tony
Click to expand...

Attachment filename: inchconversionspreadsheet-2.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=550003 ---
Message posted
Click to expand...
Click to expand...
 
Back
Top