Auto-Run Macro to Specific Cell when Target is a Formula

[kyleheney]

Hi,

I'm trying to have a macro (kyledate) auto-run to a specific cell after the user enters their name (on a different sheet). On the main sheet where I want the macro to run, the user's name is automatically populated in cell C410 when the user entered their name in Sheet 2 (using the simple +"sheet2,C5" formula).

I've tried using the Worksheet_Change VBA process to get my macro (kyledate) to auto-run, but since cell C410 is a formula, it doesn't work. I've read that using the Worksheet_Calculate function instead will make it work, but I can't seem to get it right.

Also, I need the macro to auto-run to cell C411. Right now, it works when I fill in cell C410 and hit enter (since C411 is right underneath), but since the value in C411 will be auto-populated based on the user's input in the other sheet, I think my macro or the Worksheet_Calculate code needs to reference the cell C411 as the cell where the 'result' will be.

My current code for my Worksheet_Change is the following:

Please Login or Register  to view this content.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$410" Then
kyledate
End If
End Sub

Please Login or Register  to view this content.

**Note: this only works when C410 is NOT a formula. I need C410 to be a formula, which results in nothing happening

My macro (kyledate) is the following:

Sub kyledate()

ActiveCell.FormulaR1C1 = Now()

End Sub

**Note: kyledate also runs with a keyboard shortcut "ctrl-d", but I'm not sure if that would help at all.

Thanks for your help! I'm pretty new to all this macro stuff, so I really appreciate any help offered!

I'm trying to accomplish cell C411 as having a static date when cell C410 is filled in (C410 is automatically filled in when a cell in sheet2 is filled in)



***Sorry for not having code wrapped properly. First time posting and had no idea how. Tried editing, but to no avail.

[kyleheney]

Is this anywhere close to doing what I need? Nothing happens when I do this though.

Private Sub Workbook_Calculate()
If Sheets("sheet2").Range("C5").Value > 0 Then
Sheets("sheet1").Range("C411") = Now()
End If
End Sub

[kyleheney]

How do you run two separate "Worksheet_Change" events in one sheet? I have the following code, but it doesn't work.

Please Login or Register  to view this content.

Edit: Solved this question by using multiple If statements (learning as I go!). I'd still like some guidance on using Worksheet/Workbook_Calculate so that my code can reference a cell with a formula and still work.