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:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
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

Board Regular
Joined
Jul 7, 2008
Messages
77
Sorry, I am not great with VBA, should I replace Target.Row and Target.Column with my cell references?
 

in_d

New Member
Joined
Mar 3, 2009
Messages
47
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

Well-known Member
Joined
Jan 27, 2009
Messages
983

ADVERTISEMENT

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

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

Well-known Member
Joined
Jan 27, 2009
Messages
983

ADVERTISEMENT

James,

What is the cell ref you want to monitor

Colin
 

jkharmer

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

Well-known Member
Joined
Jan 27, 2009
Messages
983
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,314
Members
414,053
Latest member
Dual Showman

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
Top