Event macro?

K

Kent

Is there a way to have a macro automatically run when
dates are entered in cell B1 thru D1?

Any help help would be awesome, thanks.
 
J

JE McGimpsey

One way:

Enter this in the worksheet code module (right-click on the worksheet
tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("B1:D1")) Is Nothing Then _
If IsDate(.Value) Then _
MyMacro
End With
End Sub
 
I

ijb

Have a look at Worksheet_SelectionChange event (right-click the worksheet in
the VB editor and select "view code")
This event triggers every time any cell is changed.
You can then interrogate the "target" range that excel provides to the
event. "Target" is the range of cells changed.

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help
 
K

Kent

Works very nice, but can the event be triggered when both
cells are changed? the macro is triggered when any cell
from range B1:D1 is entered with a date. I want the user
to be able to enter a time parameter.

Thanks for helping
 
J

JE McGimpsey

What do you mean by "both"? There are *three* cells in B1:D1...

Does it matter which is changed first? What if the user wants to use the
existing time with a new date?

It's always helpful to put list all the requirements at the beginning...
 
E

Earl Kiosterud

Kent,

Here's a barebones routine that waits for all three cells to change, then
runs your code:

Private Sub Worksheet_Change(ByVal Target As Range)
Static Date1 As Boolean, Date2 As Boolean, Date3 As Boolean
If Not Intersect(Range("B1"), Target) Is Nothing Then ' first cell
Date1 = True
End If
If Not Intersect(Range("C1"), Target) Is Nothing Then ' first cell
Date2 = True
End If
If Not Intersect(Range("D1"), Target) Is Nothing Then ' first cell
Date3 = True
End If

If Date1 = True And Date2 = True And Date3 = True Then
MsgBox "do your code here"
Date1 = False
Date2 = False
Date3 = False
End If
End Sub

The problem is that if the user wants to change only one or two, then go, it
won't go. And it fires when they're cleared (deleted) too. Should not a
button be used to run your code instead?
 
Top