Not sure what debug error you're getting. I would change the code slightly as it is not very efficient nor robust. I would suggest using something similar to this, although there isn't any *strong* error handling with it...

You can't actually log any formatting changes taken place, as it won't set off a change event. Values changing, yes, formats, no.

I'm guessing your issue number 3 is from your use of ActiveCell where you're using that to put the picker value, but I'm not sure. It's not recommended to use ActiveCell though, unless you actually need that for a specific reason. Can you explain how/why you're using that control?

I have to run at the moment but will look at your other issues later.

Regards,
Zack Barresse

0

ckwillGWUAuthor Commented: 2012-04-05

Zack,

I'm not exactly sure why the ActiveCell is there for the date picker, as I downloaded this example from the link listing in the descript. However, the sheet1 code was updated as follows --

If Not Intersect(Target, [G:G]) Is Nothing Or Not Intersect(Target, [H:H]) Is Nothing Then
Cancel = True
UserForm1.Show
End If
End Sub

I'm not having the issue any longer with the date picker. Also, my other issues have been resolved.

I have to update with your suggestions and see if that will solve my last issue in getting the date and time stamp with this running log.

Thanks for your input and taking a look into this...

0

ckwillGWUAuthor Commented: 2012-04-05

So far looks good, but when I added to my sheet, I noticed the usernane is duplicated (in column b and in column c). Also, is there a way to keep track of what was changed or updated (in additon to the additions) as was previously done in the other code (e.g., jordan changed cell $H$3 from 1/29/1900 to 5/18/2012)?

So mots times this will be the case. You may get times when someone puts a different name when they register Office, or people who change the names, or those who put company names. They give you two very different pieces of information, but often times they are the same. I would recommend having both. When they are not equal, you will be able to spot it fairly easily (or run a script on it, or filter, etc) and you'll be the wiser for it, whereas if you don't, you won't be the wiser.

Also, is there a way to keep track of what was changed or updated (in additon to the additions) as was previously done in the other code (e.g., jordan changed cell $H$3 from 1/29/1900 to 5/18/2012)?

So instead of putting the information into one cell, my code breaks it out into multiple columns. I would recommend this way. For the reason above as well as normalization. It will be easier to create reports and find information this way. You will be able to filter on it and Pivot the data fairly easily. If you really want to keep it in one cell, you can do so, but I would recommend against doing so. The way I posted will be easier and better for getting data out of. We can do either way for you though, so just let us know.

Regards,
Zack Barresse

0

ckwillGWUAuthor Commented: 2012-04-05

I agree with you on the users' name -- that makes perfect sense.

On the tracking changes in separate cells, I received blank cells for that part. I attached a version of the spreadsheet where I updated with your code.

Here's my current iteration ---
I discovered if you insert rows/columns you could get an error. I modified Zach's code to align with your log sheet, and incorporate all prior changes from previous questions, so you can fully test, including the datepicker dropdown right on top of the cell.

Also previousValue is a variant, and there needs to be error checking around that and if you're going to use Zach's code which I believe a good value add, then we need to first check if previousValue is an array (re: multiple cell changes are being made so need to parse the previousValue as an array on each cell being logged.

I also repaired your log sheet Date/Time logging which logs the date/time of any changes made in the log sheet, by moving the log sheet down to start at row 4, otherwise it would not log that properly or prompt an error as well.

See Worksheet_Change() code in the Log sheet.

Here's the revised code log:

In log sheet:

Private Sub Worksheet_Change(ByVal Target As Range)Dim r As RangeDim bLogChange As Boolean'may not need this as format of log sheet has changed, thus range A1 and A2 are out of context? However forcing log sheet to start lower so this can be logged For Each r In Target If Not Intersect(r, Range("$A$3:$A$2090")) Is Nothing And r.Value <> vbNullString Then bLogChange = True Exit For End If Next r If bLogChange Then Range("A1") = Date Range("A2") = Time End IfEnd Sub

Sub datePickerManager(Target As Range, bDisplay As Boolean)Dim dpkrTemp As OLEObjectDim WS As WorksheetDim vType As VariantDim chkDVList As VariantDim proceedSetup As Boolean Set WS = ActiveSheet On Error Resume Next 'connect to temporary Date Picker "TempCombo", testing along the way using Err.Number Set dpkrTemp = ActiveSheet.OLEObjects("TempDpkr") If Err.Number <> 0 Then 'the Date Picker object must have been inadvertently deleted, so let's create it Set dpkrTemp = ActiveSheet.OLEObjects.Add(ClassType:="MSComCtl2.DTPicker.2") dpkrTemp.Name = "TempDpkr" End If On Error GoTo errHandler If bDisplay Then With dpkrTemp .Left = Target.Left .Top = Target.Top .Visible = True .Width = Target.Width + 5 .Height = Target.Height + 5 .LinkedCell = Target.Address .Activate End With Else With dpkrTemp 'hide the Date Picker, and get it out of the way from inadvertent deletion If .Visible = True Then .Visible = False .Left = Range("BB5000").Left .Top = Range("BB5000").Top End If End With End IferrHandler: 'do Nothing for the momentEnd Sub

Minor modification - on adding new row to Sheet1, also check column I and if blank, put the On Hold warning statement there:

in Sheet1 codepage:

Private Sub Worksheet_Change(ByVal Target As Range)Dim lastRow As Long Call logResults(Target) If Target.Count > 1 Then Exit Sub 'not handling at the moment - this is if more than one cell is changed simultaneously If Not Intersect(Target, Range("F:F")) Is Nothing Then If Target.Value = "On Hold" And Left(Range("I" & Target.Row).Formula, 1) <> "=" Then 'prompt user MsgBox "Please Enter comments for On HOLD status" End If End If lastRow = Range("A:I").Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row If Target.Row = lastRow Then 'A change was made on another row than the last row, so skip If Target.Worksheet.Cells(lastRow, "H").Value = vbNullString Or Target.Worksheet.Cells(lastRow, "I").Value = vbNullString Then Application.EnableEvents = False If Target.Worksheet.Cells(lastRow, "H").Value = vbNullString Then Target.Worksheet.Cells(lastRow, "H").Formula = "=IF(UPPER(LEFT(F" & Target.Row & ",6)) = ""STATUS"",IFERROR(VLOOKUP(F" & Target.Row & ",StatusDaysAdvanced,3,0),0)+G4,"""")" End If If Target.Worksheet.Cells(lastRow, "I").Value = vbNullString Then Target.Worksheet.Cells(lastRow, "I").Formula = "=IF(F" & Target.Row & "=""On HOLD"",""Enter comments for On HOLD status"","""")" End If Application.EnableEvents = True End If End IfEnd Sub

I guess I did more than what you wanted, re: 200 points, etc., but felt compelled to pull all this together as I assisted on build up questions to this. Zack - sorry to hijack the thread, lol.

This is not the first request to audit sheet changes so I was intrigued. Nice bit of logic you found in that original tip and now I can point to the code we've helped you build up to this, based on all this stuff in the knowledgebase.

Ok. Thanks both Zack and Dave. I'm going to go through and combine aspects of all the workbooks into one file. Once completed, I'll post for review of any minor kinks or tweeks needed. Today, I plan on closing my current two posts open and adding two knew ones - for this same workbook. The first will be related to providing a link back to the original Sheet1 record from the Report created .. And, then a the last one will be for any minor issues with everything working seemlessly together. I may not get to this until Monday... Enjoy your weekend! Thanks again for your help.