Simplified direction to Automatically run Macros when a cell changes

Re: Simplified direction to Automatically run Macros when a cell changes

something like this?
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Me.test
End Sub

Sub test()
MsgBox "Yep!"
End Sub[/vba]

Re: Simplified direction to Automatically run Macros when a cell changes

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1, 1)
If .Address(0, 0) <> "C5" Then Exit Sub
If .Value <> 14 Then Exit Sub
fourteen ' assuming the subroutine is in standatd module
End With
End Sub[/vba]

Re: Simplified direction to Automatically run Macros when a cell changes

use Select case statement
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1, 1)
If .Address(0, 0) <> "C5" Then Exit Sub
Select Case .Value
Case 14
fourteen
Case 15
fifteen
Case 16
sixteen
End Select
End With
End Sub[/vba]

Re: Simplified direction to Automatically run Macros when a cell changes

whatever the macro you named...

Re: Simplified direction to Automatically run Macros when a cell changes

You need to replace the code

Re: Simplified direction to Automatically run Macros when a cell changes

If you want to refer back this post, go to "subscriptions" from "Forum Junp" combobox
you cann see all the posts that you concerned

Re: Simplified direction to Automatically run Macros when a cell changes

Can this be used to monitor changes to a named range, and run a macro if it changes?

Here's what I have, and it's not working.

In the "ThisWorkbook" module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.Cells(1, 1)
        If .Address(0, 0) <> "BudgetDollars" Then Exit Sub
        Call ConstructionHours
    End With
End Sub

And in a normal module:

Sub ConstructionHours()


MsgBox "Hello"
End Sub

I don't see the "Hello" when I make a change to the named range BudgetDollars.

Re: Simplified direction to Automatically run Macros when a cell changes

Slickity

Try this.

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Intersect(Target, Range("BudgetDollars")) Is Nothing Then Exit Sub 
        Call ConstructionHours 
End Sub

By the way why not start a new thread?

Re: Simplified direction to Automatically run Macros when a cell changes

Hi,
I just tried your code and it still doesn't seem to be working. Nothing happens when I change that cell.

Is it right to put the code you just provided in ThisWorkbook? Do I need any other code anywhere, besides the ConstructionHours sub code in a user module?

As for starting a new thread, I searched for threads on this problem, found this one, tried out the code that was posted, and had a question about a modified version of that code. In cases like that, I generally post within the original topic for the sake of keeping forums organized. Seems to make more sense to me. If I started a new thread, I'd want to link back to the original anyway to explain where I got the code. Does OzGrid have a rule about situations like that?

Thanks.

Re: Simplified direction to Automatically run Macros when a cell changes

In your code, should I be passing in some value or range?
If not, then shouldn't the following give me a MsgBox if I make any change?
Because it's not giving me anything right now.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "test"
End Sub

Re: Simplified direction to Automatically run Macros when a cell changes

The code should go in the worksheet module.

Re: Simplified direction to Automatically run Macros when a cell changes

I must be missing something here. I don't see a Worksheet module. Where should this be located? I see ThisWorkbook, lumped in with the sheets in the workbook, then seperately, the user modules.
I'm using Excel 2000. Is this a newer feature you're talking about?