Call A Macro

[tonyrosen]

I have a SUB called "ValidateStopDate()" that I need called in my Worksheet_Open section. However, it doesn't work out. The macro works if I just run the macro, but it won't fire off from Worksheet_Open.

Is there some special method of calling this?

[Marcster]

Hi Tony,
Try

Call ValidateStopDate

Marcster.

[tonyrosen]

My Code Is

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

"ValidateStopDate" works from a button and it works from just running the macro. However, it just doesn't fire in the _Change section.

[mvidas]

Hi Tony,

Try replacing your "Call ValidateStopDate" with just an ordinary msgbox. This will help see if the If statement is working the way you want. Personally, I think you'd be better off with a revised If statement, like: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
If Not Intersect(KeyCells, Target) Is Nothing Then
Call ValidateStopDate
End If
End SubMatt

[tonyrosen]

Replacing my macro call with a MsgBox worked like a champ.

In other developments with this:

Even if I just take my code out of "ValidateStopDate()" and code it directly into the _Change sub, it doesn't fire off either.

Is this just an odd behavior, or is it something I did? Probably both ... with my luck.

[Marcster]

When I've pasted this code into the worksheet VBE,
the ValidateStopDate macro runs when I change the contents of cell F2 and press return
to move out of the cell.
I'm using Excel 2000.

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

Sub ValidateStopDate()
MsgBox "ValidateStopDate has been run."
End Sub


I think it maybe the ValidateStopDate macro which is the problem here.
Could you post the code to look at?.

Marcster.

[tonyrosen]

The problem was in my "ValidateStopDate()" sub ...

Original piece:

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

Fixed the whole problem by:

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

[Marcster]

Glad you got it working :).

Marcster.