Check if input number is minimum and display a pop up dialogue

Closed
mmk622 Posts 1 Registration date Sunday November 30, 2014 Status Member Last seen November 30, 2014 - Nov 30, 2014 at 02:43 AM
 Murali - Dec 2, 2014 at 04:03 PM
Hi,

I am novice VBA developer. I am trying to write a macro which does following.

1.When a user enter a particular value into cell
2.Check if it is the least value in the cells already filled (if exist)
3.If it is the least value, then display a dialogue saying, the value you entered is the least value.


I want this macro to be executed automatically when I enter the value into a cell of this particular sheet.

Thanks in advance,
Murali

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 1, 2014 at 12:04 AM
I assume the data is in column A

right click the tab of sheet1 (relevant sheet) and click view code
in the window that comes up copy paste this EVENT CODE

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Target.Column <> 1 Then Exit Sub 'I assume data is in column A
Application.EnableEvents = False
Set r = Range(Target, Target.End(xlUp))
If Target = WorksheetFunction.Min(r) Then
MsgBox "what you have just now entered is minimum so far"
End If
Application.EnableEvents = True
End Sub

now go the lasts cell plus one enter a number and see what happens
if it is not minimum nothing happens other wise a pip up message will come up

do some experiments with some sample data.
0
Hi Venkat,

I see the code works only for column 1.

Is it possible to change the code as below.

1. When I enter a number in column 1 it should check if the value entered is the minimum and display.
2. When I enter a number in column 2 it should check if its the minimum in column 2 and display message
3. when I enter a number in column 3 it should check if it is the minimum in column 3 and display message.

and so on.

To be generic. When a number entered in nth column , it should be checked if its the minimum in that column and a message should be displayed.

Could you help with this scenario please ?

Murali
0