How to run a macro based on cell value

  • Thread starter Thread starter Haleigh
  • Start date Start date
H

Haleigh

Hi all,

I've read http://www.cpearson.com/excel/events.htm to help
me understand how to trigger a macro based on a cell
value, but can't get it to work. I've copied the code at
the web site and it in "Module 1".

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "R32" Then
Application.EnableEvents = False
If Target.Value >37 Then
MsgBox "Total hours cannot be greater than 37!"
End If
Application.EnableEvents = True
End If

End Sub

In English, what I'm asking is, if the value of
(calculated) cell R32 is greater than 37, then display the
message box.

In the Visual Basic editor window, if I click Run, a
dialog comes up asking me for the name of the macro. The
macro Worksheet_Change doesn't appear. There might be
something else wrong, but since I can't get it to even
run, it can't be tested. Help doesn't tell you anything
about how to run a macro other than by the Run command.

Any advice will be deeply appreciated.

Haleigh
Note: This is a re-post of a message I accidentally sent
to the MS Access forum yesterday.
 
Hi
this code has to go in the worksheet module and not
in 'module1'. So try the following:
- right-click on the sheet tab
- select 'Code'
- paste the code

Frank
 
Thank you for helping me, Frank. I did as you said (except
the option when I right-clicked the sheet tab was "View
Code", not "Code". I protected the sheet and entered some
numbers that summed >37, but the macro didn't fire. I
tried unprotecting the sheet, and still nothing.

Could you help again soonest?

Haleigh

P.S. Where on earth does Excel Help explain about putting
macros in the sheet?! I would not have ever guessed this!!
What are the rules about putting macros in a worksheet
versus via the Tools, Macro, Record, etc. menu?
 
There are 3 problems here:
1) This macro is designed to run automatically when the worksheet is
updated. That is why you don't see it in the macro list.
2) The value of Target.Address is an absolute address, so you would need to
compare it with "$R$32"
3) You state that the value of R32 is calculated. The worksheet_change input
range is only cells that have been physically changed, not where only their
values have changed.

In my worksheet, cell R37 (on sheet 1) contains a formula =L4

So I coded the following macro and inserted it in the Sheet1 Object

Public Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$L$4" Then
Application.EnableEvents = False
If Range("R32") > 37 Then
MsgBox "Total hours cannot be greater than 37!"
End If
Application.EnableEvents = True
End If


End Sub
 
Or, to remove the dependency on which cell was actually updated, do:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Range("R32") > 37 Then
MsgBox "Total hours cannot be greater than 37!"
End If
Application.EnableEvents = True

End Sub
 
Hi
try changing the line:
If Target.Address = "R32" Then

to
If Target.Address = "$R$32" Then
 
Thanks, Paul. I'm beginning to feel real dense and
frustrated but I still can't get it to work.

Right now, I have the following. Note some of the row
numbers have changed since my original post.)

Cell Q32 contains the formula to add Total Hours:
=SUM(Q9:Q31)

Not sure what your reference to L4 is, but assuming it is
the value of my cell Q32, I've added the following in cell
Q33:
= Q32

If the Value of Q32 (or the calculated sum of Q32) is
greater than 37, then display the message box:

Public Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$Q$32" Then
Application.EnableEvents = False
If Range("Q33") > 37 Then
MsgBox "Total hours cannot be greater than 37!"
End If
Application.EnableEvents = True
End If

End Sub

Paul, are my references messed up? Also, I am physically
protecting and unprotecting the worksheet and it doesn't
make any difference. If (and when -- sigh!) I can get this
to work, will I have to code something else to remove
protection and put it back on again? If yes, how can you
write a macro to turn off protection so the macro can run
when the worksheet is protected and can't run the macro to
begin with?

Confused,
Haleigh
 
Back
Top