Now that we have code that does what you want once, let's make it dance the way that you want it to.
This is the Recorder's code, with comments. Also, since VBA doesn't need to select cells with a mouse, the Selecting has been removed
Code:
ActiveCell.FormulaR1C1 = "1-Aug-2011": Rem user enters date in Column A
Rem formula is put in column B
Range("B2").FormulaR1C1 = "=RC[-1]-(WEEKDAY(RC[-1],3))"
Rem which is then copy/paste valued
Range("B2").Copy
Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Rem same for column C
Range("C2").FormulaR1C1 = "=TEXT(RC[-2],""mmm""""'""""yy"")"
Range("C2").Copy
Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Let's base everything off of the ActiveCell, by using a With...End With construct.
Code:
ActiveCell.FormulaR1C1 = "1-Aug-2011"
With ActiveCell
.Offset(0, 1).FormulaR1C1 = "=RC[-1]-(WEEKDAY(RC[-1],3))"
.Offset(0, 2).FormulaR1C1 = "=TEXT(RC[-2],""mmm""""'""""yy"")"
.Offset(0, 1).Resize(1, 2).Value = .Offset(0, 1).Resize(1, 2).Value
End With
.Offset(0,1).Resize(1, 2) is the range that starts one cell to the right and is one row by two column.
If ActiveCell was A2 (like when you recorded the macro), .Offset(0,1).Resize(1, 2) would be B2:C2.
The .Value = .Value is a quick way to do the same as copy/PasteValues.
Since you want this triggered everytime the user enters something into column A, we would put our routine in the Worksheet_Change event. This is found in the sheet's code module, rather than the normal module where the Macro Recorder put it's routine.
When in the VB Editor, look at the project Explorer and double click on the icon for the sheet where you want this to occur.
When that window opens, choose Worksheet from the left dropdown at the top of the window and then Change from the right drop down (not SelectionChange)
The window should have created an empty Worksheet Change event that looks like this. Just waiting to be filled with code.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
This routine will run any time any cell in the sheet is changed, but you only want action to be taken when a date is entered in column A. To make this restriction, we use the Target argument. Target is those cells that are changed. We want to code to run when a) only one cell is changed, b) that cell is in column A and c) a date is what is entered. So we test for those conditions
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 1 And IsDate(Target.Value Then
Rem code
End If
End Sub
Putting it all together, we get
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Goto ErrorOut
With Target
If .Cells.Count = 1 And .Column = 1 And IsDate(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).FormulaR1C1 = "=RC[-1]-(WEEKDAY(RC[-1],3))"
.Offset(0, 2).FormulaR1C1 = "=TEXT(RC[-2],""mmm""""'""""yy"")"
.Offset(0, 1).Resize(1, 2).Value = .Offset(0, 1).Resize(1, 2).Value
End If
End With
ErrorOut:
Application.EnableEvents = True
End Sub
The Application.EnableEvents is set to false so that the changes that the code makes don't trigger another Change event, which would trigger another Change event, etc...... setting EnableEvents to false keeps the Change event from running. But it needs to be set to True at the end, so that it will run the next time the user enters data.
The Error code is so that if an error occurs while EnableEvents is false (like if the user protects the sheet), if that occurs, the error will be handled and EnableEvents will be set to True.
I know this is a pretty big chunk of info to absorb, but if you can understand this, you'll be able to do it by yourself, from Macro Recorder to error protected event code, the next time.
