Macro to bring up a pop up box

[jkharmer]

Hi,

I was wondering how I would go about writing a macro that would pop up if a value in a certain cell is greater than a set value. For example, if the cell b1 has a value of >50, I would want an alert box to pop up saying "Over Range". Ideally this should be automatic as soon as another value is entered into cell a1.

Any help would be great, Thanks, James

 

[ColinKJ]

Hi jkharmer,

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(Target.Row, Target.Column).Value > 50 Then
MsgBox ("Value Is Over Range")
End If
End Sub

ColinKJ

 

[jkharmer]

Sorry, I am not great with VBA, should I replace Target.Row and Target.Column with my cell references?

 

[in_d]

nope.. press alt+F11 to bring up the VBA Editor. If you are entering the values into the worksheet called "Sheet 1" double click on sheet1 and paste the code as above.

That's all you need to do.

 

[ColinKJ]

Hi

You just need to paste the code

If Cells(Target.Row, Target.Column).Value > 50 Then
MsgBox ("Value Is Over Range")
End If


into the Private Sub Worksheet_Change(ByVal Target As Range)
of the particular sheet you are working with.

The "Cells(Target.Row, Target.Column)" is any cell on that sheet that a value has been entered in.

ColinKJ

 

[jkharmer]

Ah, I see.

There are loads of other cells in the sheet, but it is just this one specifc cell I want the macro to look at. It is not a case of saying "if any of them are over 50 then bring up an alert". How would I change the text to just look at a single cell?

James

 

[ColinKJ]

James,

What is the cell ref you want to monitor

Colin

 

[jkharmer]

Actually 5 cell numbers:

Q1016, T1016, W1016, Z1016, AC1016.

At the moment, these cells have the word "INPUT" in them. If someone types a number into one of these cells, this number is used to make a calculation in cells.

Q1021, T1021, W1021, Z1021, AC1021.

I just want a reminder to pop up that when someone enters a number in any of the 1016 cells, to check the value in the 1021 cells.

Does that make sense?

 

[ColinKJ]

Hi jkharmerive this one a try

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 17, 20, 23, 26, 29
Select Case Target.Row
Case 1021
If Cells(Target.Row, Target.Column).Value > 50 Then
MsgBox ("Value Is Over Range")
End If
End Sub
End Select
End Select
End Sub


Colin

 

[jkharmer]

Sorted, thanks for your help.

James