Macro to bring up a pop up box

jkharmer

Board Regular
Joined
Jul 7, 2008
Messages
77
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
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
Sorry, I am not great with VBA, should I replace Target.Row and Target.Column with my cell references?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,056
Messages
6,105,650
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top