p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   Excel VBA Check When Cell Has Changed (http://p2p.wrox.com/showthread.php?t=35120)

tonyrosen November 14th, 2005 10:49 PM

Excel VBA Check When Cell Has Changed
 
Okay, maybe I'm just stupid ...

Let's say cell "A1" and "A2"

I want to check if "A2" changes and if so, run a macro ...

I can't get the event to fire off and, I'm sure it's because I have no clue what I'm doing.

Help ... please.


vemaju November 15th, 2005 02:41 AM

HI Tony

Put this code to Worksheet Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
    'Do some coding here
End If
End Sub

-vemaju


tonyrosen November 15th, 2005 09:36 AM

Yea! I'm a superstar:

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Set WatchRange = Range("A1:A10")
    Set IntersectRange = Intersect(Target, WatchRange)
    If IntersectRange Is Nothing Then
        'Do Nothing Spectacular
    Else
        Call MyMacro
    End If
End Sub


tonyrosen November 15th, 2005 01:03 PM

Argh!

It won't call my Macro ... The Worksheet_Change works, but it won't call the macro ... is there a syntax for that?


mjppaba November 15th, 2005 01:09 PM

Just a quickie,

above works fine for me, can you go into Excel and check the following...

In Menu [Tools]...[Macro]...[Security]

What setting do you have?

Matt


tonyrosen November 15th, 2005 02:23 PM

ub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Set WatchRange = Range("A1:A10")
    Set IntersectRange = Intersect(Target, WatchRange)
    If IntersectRange Is Nothing Then
        'Do Nothing Spectacular
    Else
        Call MyMacro 'Where MyMacro is the name of my macro.
    End If
End Sub



tonyrosen November 15th, 2005 06:00 PM

Okay, here's my SUB

=============

Sub ValidateStopDate()
    MyRow = ActiveCell.Row
    'This is the Stop Date Column Number
    MyColumn = ActiveCell.Column
    'This is the Start Date Column Number
    MyColumnPrev = MyColumn - 1

    If MyColumn > 26 Then
        MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65)
    Else
        MyColumnLetter = Chr(MyColumn + 64)
    End If
    'MyColumnLetter = Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$")) - 2)


    MyColumnNext = MyColumn + 1
    If MyColumnNext > 26 Then
        MyColumnLetterNext = Chr(Int((MyColumnNext - 1) / 26) + 64) & Chr(((MyColumnNext - 1) Mod 26) + 65)
    Else
        MyColumnLetterNext = Chr(MyColumnNext + 64)
    End If

    MyRange = Range(MyColumnLetter & MyRow)
    MyNextRange = Range(MyColumnLetterNext & MyRow)

    Range("H3") = MyNextRange
    'Range("G3") = MyColumnLetterNew

    'MyNewRange = Column F
    'MyRange = Column E
    'If Year(F) + 1 > Year(E)
    StopMonth = Month(MyRange)
    StopDay = Day(MyRange)
    StopYear = Year(MyRange)
    YearOne = MyNextRange

    If StopYear < YearOne Then
        'StopDate is no good
        MsgBox ("Your Stop Date cannot occur prior to 12/31/" & YearOne & ".")
    Else
        If StopYear = YearOne Then
            If StopMonth < 12 And StopDay < 31 Then
                'StopDate is no good
                MsgBox ("Your Stop Date cannot be sooner than 12/31/" & YearOne & ".")
            Else
                'StopDate is after 12/31/YearOne
                Range("H3") = "OKAY"
            End If
        End If
    End If
End Sub

=============

And, my Change ...

=============

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("F2")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Call ValidateStopDate
    End If
End Sub

===================

It won't run the macro call ... help(?)


tonyrosen November 16th, 2005 10:32 AM

On top of that, if I place a button on the page to run the code, it works perfectly as well.


shattered November 16th, 2005 11:36 AM

need to evaluate whats going wrong here...

The procedure does work so two questions ?

1) is the code in the actual worksheet (ie not the workbook or a module)?

2) is calculation set at automatic?


tonyrosen November 16th, 2005 11:52 AM

Figured it out ... I was "finding" the wrong cells. My "active" cell was REALLY the desired "active" cell plus one ... Here is the piece I fixed:

    MyRow = ActiveCell.Row
    'This is the Stop Date Column Number
    MyColumn = ActiveCell.Column - 1
    'This is the Start Date Column Number
    MyColumnPrev = MyColumn - 2



All times are GMT -4. The time now is 03:57 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.