To view a conditional msg box

0

How to view a mssg box when a cell reaches on its limit we fix.?

Answer
Discuss

Discussion

How does one fix the limit of a cell? Have you considered Data Validation?
Variatus (rep: 1815) Apr 27, '17 at 10:50 pm
I dont know what data validation is.? Plz explain
Sankalp (rep: 2) Apr 28, '17 at 12:02 am
Add to Discussion

Answers

0
Selected Answer

You can use a macro to do this.

If the cell that will reach a limit contains a formula, then you need this macro:

Private Sub Worksheet_Calculate()

set_limit = 5

If Range("A1").Value = set_limit Then

    MsgBox "Limit reached!"

End If

End Sub

Change A1 to the cell that should have the limit. Change 5 to whatever limit you want.

If the cell will reach its limit by someone actually entering a value into it, use this code:

Private Sub Worksheet_Change(ByVal Target As Range)

set_limit = 5

If Target.Address = "$A$1" And Target.Value = set_limit Then

    MsgBox "Limit reached!"

End If

End Sub

Change $A$1 to the cell that should have the limit. Change 5 to whatever limit you want.

Both of these macros should go into the worksheet module. When you hit Alt+F11, double-click the sheet name where the cell that should have a limit is located and paste the desired code.

Discuss
0

From the 'Data' tab, select 'Data Validation' -> 'Data Validation'.

On the first ('Settings') tab select one of the methods by which you can set limits, such as numbers, length of text etc. You can even set a list (which could be an Excel sheet range), to allow only specific words. Depending upon which method you select there will be more criteria you can enter, such as specifying a minimum and maximum namer. For all of them you can specify whether a blank cell is acceptable or an error.

The second tab allows you to specify an input message. That is a mesage which will be displayed when you click on the cell. You can specify no message. The default is to show a message, but no message is specified. (Same difference.)

On the third and final tab you can specify what happens if the suer enters something that isn't acceptable. You can add or remove the checkmark against "Show error alert after invalid data is entered" and then you can specify a messaebox, complete with Title, Icon and Message.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login