View Full Version : Call A Macro
tonyrosen
11-16-2005, 07:17 AM
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
11-16-2005, 07:42 AM
Hi Tony,
Try
Call ValidateStopDate
Marcster.
tonyrosen
11-16-2005, 08:02 AM
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
11-16-2005, 08:09 AM
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
11-16-2005, 08:19 AM
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
11-16-2005, 08:23 AM
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
11-16-2005, 08:54 AM
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
11-16-2005, 09:10 AM
Glad you got it working :).
Marcster.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.