I need help with automatically assigning "now" to a cell in sheet2

%3CLINGO-SUB%20id%3D%22lingo-sub-3119526%22%20slang%3D%22en-US%22%3EI%20need%20help%20with%20automatically%20assigning%20%22now%22%20to%20a%20cell%20in%20sheet2%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119526%22%20slang%3D%22en-US%22%3E%3CP%3EMaybe%20challenging%20to%20explain%2C%20i'll%20try-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20Sheets.%20The%20active%20sheet%20is%20sheet%201%2C%20and%20that's%20where%20I%20need%20folks%20to%20change%20%22comments%22.%26nbsp%3B%20I%20need%20the%20date%20of%20those%20changes%20recorded%20on%20the%20very%20same%20cell%20number%2C%20plus%201%20(so%20offset%20of%200%2C1)%20but%20on%20sheet2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20two%20sheets-%20one%20called%20%22Main%22%2C%20the%20other%20called%20%22Info.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsers%20stay%20on%20%22Main%22%20and%20update%20comments%20in%20the%20cells%20of%20column%20K%20about%20the%20status%20of%20listed%20projects.%26nbsp%3B%20%26nbsp%3BOn%20sheet%20%22Info%2C%22%20I%20want%20those%20comments%20to%20appear%20in%20%22info%22%20column%20K%2C%20and%20the%20dates%20to%20appear%20in%20%22info%22%20column%20L.%26nbsp%3B%20BUT-%20I%20need%20sheet%20%22Main%22%20to%20be%20the%20active%20sheet%20at%20all%20times.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPossible%3F%26nbsp%3B%20I'm%20stumped%2C%20hopefully%20it's%20a%20matter%20of%20a%20command%20line%20or%20something%20that%20I%20have%20never%20encountered.%20I'm%20VERY%20new%20to%20VB.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20that%20puts%20the%20date%20in%20the%20right%20positions%20on%20%22MAIN%22%2C%20but%20I%20need%20it%20to%20be%20on%20%22INFO%22%20and%20not%20MAIN.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20WorkRng%20As%20Range%3CBR%20%2F%3EDim%20Rng%20As%20Range%3CBR%20%2F%3EDim%20xOffsetColumn%20As%20Integer%3CBR%20%2F%3ESet%20WorkRng%20%3D%20Intersect(Application.ActiveSheet.Range(%22K%3AK%22)%2C%20Target)%3CBR%20%2F%3ExOffsetColumn%20%3D%201%3CBR%20%2F%3EIf%20Not%20WorkRng%20Is%20Nothing%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EFor%20Each%20Rng%20In%20WorkRng%3CBR%20%2F%3EIf%20Not%20VBA.IsEmpty(Rng.Value)%20Then%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).Value%20%3D%20Now%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).NumberFormat%20%3D%20%22dd-mm-yyyy%2C%20hh%3Amm%3Ass%22%3CBR%20%2F%3EElse%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).ClearContents%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ESet%20WorkRng%20%3D%20Intersect(Application.ActiveSheet.Range(%22G%3AG%22)%2C%20Target)%3CBR%20%2F%3ExOffsetColumn%20%3D%205%3CBR%20%2F%3EIf%20Not%20WorkRng%20Is%20Nothing%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EFor%20Each%20Rng%20In%20WorkRng%3CBR%20%2F%3EIf%20Not%20VBA.IsEmpty(Rng.Value)%20Then%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).Value%20%3D%20Now%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).NumberFormat%20%3D%20%22dd-mm-yyyy%2C%20hh%3Amm%3Ass%22%3CBR%20%2F%3EElse%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).ClearContents%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20can%20I%20just%20some%20change%20the%20address%20at%20the%20lines%20that%20read%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Not%20VBA.IsEmpty(Rng.Value)%20Then%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).Value%20%3D%20Now%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).NumberFormat%20%3D%20%22dd-mm-yyyy%2C%20hh%3Amm%3Ass%22%3CBR%20%2F%3EElse%3CBR%20%2F%3ERng.Offset(0%2C%20xOffsetColumn).ClearContents%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20how%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EArgh!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20anyone%20reading%20this%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3119526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Maybe challenging to explain, i'll try-

 

I have two Sheets. The active sheet is sheet 1, and that's where I need folks to change "comments".  I need the date of those changes recorded on the very same cell number, plus 1 (so offset of 0,1) but on sheet2.

 

In other words, two sheets- one called "Main", the other called "Info."

 

Users stay on "Main" and update comments in the cells of column K about the status of listed projects.   On sheet "Info," I want those comments to appear in "info" column K, and the dates to appear in "info" column L.  BUT- I need sheet "Main" to be the active sheet at all times. 

 

Possible?  I'm stumped, hopefully it's a matter of a command line or something that I have never encountered. I'm VERY new to VB.

 

 

Here is the code that puts the date in the right positions on "MAIN", but I need it to be on "INFO" and not MAIN.

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("K:K"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
Set WorkRng = Intersect(Application.ActiveSheet.Range("G:G"), Target)
xOffsetColumn = 5
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

So can I just some change the address at the lines that read :

 

If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If

 

 

If so, how???

 

Argh!

 

Thanks for anyone reading this far.

 

0 Replies