On 2002-04-05 05:24, Derek wrote:
Hi Bob
The event macro I gave you refers to the name of your normal module macro (it is not part of the event macro). You need to refer to the name of that macro as it appears at the top of its code and enclose it in double quotes like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then Application.Run "DoM()"
End Sub
This event macro is then just a trigger that sets off your module macro "DoM()" when you make a change in G4. This is a more flexible way to do it since you can then also run "DoM()" independantly via a button or whatever.
If you want the whole lot as an event macro word it like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "G$4" Then
Dim DayOfMonth As String
Range("G4").Select
DayOf = Format(Day(ActiveCell.Value), "dd") + 1
' MsgBox (DayOf)
If (DayOf > 31) Then DayOf = 33 - DayOf
' MsgBox (DayOf)
If (DayOf < 10) Then DayOfMonth = "0" & DayOf Else DayOfMonth = DayOf
' MsgBox (DayOfMonth)
Range("E34").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/SUM('01:" & DayOfMonth & "'!R[-30]C)"
Range("E35").Select
ActiveCell.FormulaR1C1 = "=SUM('01:" & DayOfMonth & "'!R[-30]C)"
...
End If
End Sub
Note that because the first line ie - "If Target.Address = "G$4" Then" - goes to a new line after "Then" you will need to add the line "End If" after your "DoM()" code, as a statement that it has reached the end of the action which was dependant upon the if condition.
Hope this makes sense
regards
Derek
Derek,
I have tried it both ways you suggested. I can not get the .change event to execute. G4 is a date field, if that matters. I want to change the date, let's say from 2/11/02 to 2/16/02. Then take the 16 and place it into formulas. The only way I can get it to work is to change date (G4) and run DoM manually. I have uncommented MsgBox statements within event version and get nothing...What silly thing am I missing????
TIA