VBA Macro

[Klturi421]

I am looking to have cell B1 update with the current date anytime any of the cells are updated on the workbook. Naturally, I don't want to cause an infinite loop with updating B1.

I know I've seen it done before, I just cant figure out where the macro is that I used for it.

Thanks in advance.

 

[tygrrboi]

You mean if any sheet is changed, that sheet gets a value in B1? or one specific sheet gets a value in B1?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Application.EnableEvents = False
    Sh.Range("B1").Value = Format(Now(), "mm/dd/yyyy")
[COLOR=#008000]    'alternatively, use Sheets("sheetname").Range("[/COLOR][COLOR=#008000]B1") = Format(Now(), "mm/dd/yyyy")[/COLOR]
Application.EnableEvents = True


End Sub

 

[Michael M]

maybe this in the sheet module

Sub worksheet_change(ByVal target As Range)
Application.EnableEvents = False
Range("B1").Value = Now()
Application.EnableEvents = True
End Sub

 

[Klturi421]

My apologies, only the sheet that is being updated at the time.

Also, I tried using the code you provided but it doesn't seem to be working. Am I missing anything? I tried using the alternate one that you provided and changed the "sheetname" to the appropriate sheetname but it still did not work.

 

[tygrrboi]

My question is... does this apply to all sheets? or just the one sheet?
I know its only one at a time... but do you want for example, an edit in Sheet1 to show in Sheet1!B1 and an edit in Sheet2 to show in Sheet2!B1? Or do you just want edits in sheet1 to show and ignore edits in other sheets?

I have edited my original response because I hadnt tested it and it was resulting in an error... but the above should work if it applies to all sheets. It needs to go into the code for ThisWorkbook.

If it only applies to one sheet... I would go with Michael's code in that sheet's VBA window.

 

[Klturi421]

My question is... does this apply to all sheets? or just the one sheet?
I know its only one at a time... but do you want for example, an edit in Sheet1 to show in Sheet1!B1 and an edit in Sheet2 to show in Sheet2!B1? Or do you just want edits in sheet1 to show and ignore edits in other sheets?

I have edited my original response because I hadnt tested it and it was resulting in an error... but the above should work if it applies to all sheets. It needs to go into the code for ThisWorkbook.

If it only applies to one sheet... I would go with Michael's code in that sheet's VBA window.

As I stated in my reply initially, it only applies to the sheet being updated. I tried both codes that were provided and it still does not work for me. I'm not sure what I'm doing wrong besides updating the sheetname section.

I will keep trying and report back with my result.

 

[Michael M]

where are you pasting the code ??
It needs to be pasted in the relevant Sheet module .......NOT this workbook module !!
My code will not need to be modified

 

[tygrrboi]

You have two codes now. Between the one I provided and the one Michael provided you have a solution.

You can now either google or youtube how and where to put a worksheet_change event or workbook_sheetchange event so you can be sure its in the right place.

Another option, if you are certain that it is in the correct place and it is not running, is to make sure EnableEvents is on from the start. Otherwise neither of those will run.

In your VBA Immediate Window, type
Application.EnableEvents = True
hit enter then go back to your sheet to test it out.