• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA for status update

B

bfraser

Member
I copied/pasetd the following code from another website to enter the date when something is updated/entered. So if anything is entered in cells the corresponding cell in col B is updated w/NOW(). Doesn't seem to work. Any suggestions? Keep it simple 'cause its 2am and VBA is almost Greek to me, not quite.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target

If .Count > 1 Then Exit Sub

If Not Intersect(Range("A6:A114"), .Cells) Is Nothing Then

Application.EnableEvents = False

If IsEmpty(.Value) Then

.Offset(0, 1).ClearContents

Else

With .Offset(0, 1)

.NumberFormat = "dd mmm yyyy hh:mm:ss"

.Value = Now

End With

End If

Application.EnableEvents = True

End If

End With

End Sub
 
bfraser,


You need to put the code in the Workbook_SheetChange event


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

With Target

If .Count > 1 Then Exit Sub

If Not Intersect(Range("A6:A114"), .Cells) Is Nothing Then

Application.EnableEvents = False

If IsEmpty(.Value) Then

.Offset(0, 1).ClearContents

Else

With .Offset(0, 1)

.NumberFormat = "dd mmm yyyy hh:mm:ss"

.Value = Now

End With

End If

Application.EnableEvents = True

End If

End With

End Sub


Also note this will only start the NOW() input from Cell A6 onwards.


HTH

~Vijay
 
BFraser

This code must be copied into a code page for the sheet you want it to apply to


It then looks at if you have entered anything into the range A6:A114 of the current sheet and if you have changes the value in Column B of the same row to Now and changes the cell format to dd mmm yyyy hh:mm:ss


If you enter anywhere else or enter a blank in that range nothing happens


Is that what is meant to happen?, because it works as I've described above ok, in Excel 2007+
 
vj/hui,

Thanks its working. I put the code in the workbook...


Hui,

Good point, can I add additional ranges i.e. Range("A6:A114,G6:G114.."). Knowing VBA it's probaly not that simple
 
bfraser,


Yes,


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

With Target

If .Count > 1 Then Exit Sub

If Not Intersect(Range("A6:A114,C6:C114"), .Cells) Is Nothing Then

Application.EnableEvents = False

If IsEmpty(.Value) Then

.Offset(0, 1).ClearContents

Else

With .Offset(0, 1)

.NumberFormat = "dd mmm yyyy hh:mm:ss"

.Value = Now

End With

End If

Application.EnableEvents = True

End If

End With

End Sub


~Vijay
 
I added
Code: 
If Not Intersect(Range("A6:A114,G6:G114,N6:N114")
, then inserted new col H and O. When data is entered into A and G it works fine, col B shows and date and time and col H shows date and time. Problem is Col N uses a formula based on data in cols. L and M, =IF(L6="","",(L6+M6)). When data is entered into col L or M col H isn't populated at all by the macro
 
bfraser,


Because this code is going to take one cell as input and then place the NOW() timestamp to the next cell.


When data is entered in Column A, cells of B gets populated.

When data is entered in Column G, cells of H gets populated.

When data is entered in Column N, cells of O gets populated.


I am not able to replicate the scenarion as you have described, If possible please mail your workbook at sharma.vijay1-at-gmail.com for me to have a look.


~Vijay
 
bfraser,


Your sheet is working fine just as it should...


I had to unprotect and found K38 and L38 onwards there is no formula for Man Hour calculation.


Rest everything is working normally.


It acted strangely on the first go and i could see 2 VBA projects with the same name.


I simply closed and re-opened the file.


Try that and let me know.


~Vijay
 
bfraser,


As advised earlier, the code works in the context of the current cell only (Target).


I have changed the code a bit for the Column O also to be populated when you make changes in Column G

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

With Target

If .Count > 1 Then Exit Sub

If Not Intersect(Range("A6:A114,G6:G114,N6:N114"), .Cells) Is Nothing Then

Application.EnableEvents = False

If IsEmpty(.Value) Then

.Offset(0, 1).ClearContents

Else

With .Offset(0, 1)

.NumberFormat = "dd mmm yyyy hh:mm"

.Value = Now

End With

If Left(Target.Address, 2) = "$G" Then

.Offset(0, 8).NumberFormat = "dd mmm yyyy hh:mm"

.Offset(0, 8).Value = Now

End If

End If

Application.EnableEvents = True

End If

End With

End Sub


Try this and let me know the outcome


~Vijay
 
Yes, the code works.. as far as col O is concened, I'd like to see the date/time when a change is made to col N. The initial code refereced col A and inserted time and date in col b upon a change in A. I edited the range for G6:G114 to insert date and time in col H upon a change in col G. Those two col worked fine. Then I wanted to insert date and time in col O when a change was made in col N. Edited range for
Code: 
N6:N114
. But date and time don't populate? Col N has a formula in it based on cols. L and M.


I need a coffee.
 
Great, Coffee is good..


However as i said earlier, the code will work in the context of the current active cell only.


Since Column N is formula based it is getting updated; however it is not the current active cell and hence "O" is not updated with the date/time stamp.


~Vijay
 
W/o seeing the workbook, would it be possible to detect a change in the cells that affect column N, (an additional intersect check?) and then place the time stamp in multiple spots, as needed?
 
You must log in or register to reply here.
Back
Top