Need help with this code - baaadly!!!

What I see immediately is a problem with:

;If (Chged.Column = 1) Then
;If (Chged.Row > 1) Then

These statements negate each other. If it's equal to 1, it can't be greater than one so the code is skipped entirely. You might want to put this in instead:

If Chged.Column >= 1 Then

Any problems just repost.

The worksheet change event is only fired if a cell is changed directly or by an external link. If you're using a form to update the worksheet then can you not put your date and time stamp code in there?

Regards,
Daniel.

Bit of misinformation there.

The change event is triggered whenever a cell value is changed, even if the change is triggered by a userform.

However, I do agree with your comment about putting the code on the userform. : I have the following code on a worksheet and will be using a "form" to fill in the worksheet. If I use the "user form" to input the data, it does not perform the code functions of data/time stamp and username stamp. If I manually populate the cells, it works fine? Can anyone suggest a change to get the date/time/username stamps to work, when the data is input with the user form? If, after entering from the form, I double click in column A, it then recognizes that the data is there, and does the requested functions? I thought that was odd, so I guess the spreadsheet code isn't seeing data there due to the way it is entered, until someone manually selects the cells in column A as specified in the code. ANY HELP WILL BE APPRECIATED :

Re: Any other ideas

::: I followed your directive and modified the code accordingly, but now I am getting errors on
:::Chged.Offset(, 12) = Application.UserName
The errors are "type mismatch", and it will sometimes go through the code to the username, and it inserts it every 15 cells all the way across the row, if I do the entry manually, but it still does not perform anything if I input data using the "form".
:

::: Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Chged As Range

For Each Chged In Target
If (Chged.Row >= 1) Then
If (Chged = "") Then
Chged.Offset(, 1) = ""
Else
Chged.Offset(, 12) = Application.UserName
Chged.Offset(, 13) = XlParamaterDataType & " " & Format(Now(), "hh:mm AM/PM")
Chged.Offset(, 14) = XlParamaterDataType & " " & Format(Now(), "m/d/yyyy")
End If
End If
Next
End Sub

Yes, I take back what I said. I was sure that a procedure changing the value of a cell would fire the event but I checked it with a test form anyway. Unfortunately, I had the userform put a value in cell A1 which meant that the code following 'If (Chged.Row > 1) Then' was not executed and I made a booboo.

Again, my apologies

Daniel. Bit of misinformation there. The change event is triggered whenever a cell value is changed, even if the change is triggered by a userform. However, I do agree with your comment about putting the code on the userform.

Re: Any other ideas

Yeah, didn't look any further than the "If"'s before. You haven't told it that the cell value should be the username etc.

You want to change the offset code as well to something like (note the difference is to add ".Value" after each offset.):

Chged.Offset(, 12).Value = Application.UserName
Chged.Offset(, 13).Value = XlParamaterDataType & " " & Format(Now(), "hh:mm AM/PM")
Chged.Offset(, 14).Value = XlParamaterDataType & " " & Format(Now(), "m/d/yyyy")

Also, you'll probably want to change that line where the value is to be set to "". Chane it to this:

Chged.Offset(, 1).Value = ""

HTH ::: I followed your directive and modified the code accordingly, but now I am getting errors on

Re: Any other ideas

The problem is that the code isn't checking that the data was entered into column 1 (as the code was in the original posting). Now, when a user makes a change in any column after row 1 the code puts a time stamp in the column 12, 13 and 14 columns to the right. This is turn causes the worksheet_change event to fire again and a time stamp is put another 12, 13 and 14 columns to the right. It doesn't take long for the code to run out of columns. This code works:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Chged As Range

For Each Chged In Target
If (Chged.Row >= 1 And Chged.Column = 1) Then
If (Chged = "") Then
Chged.Offset(, 1) = ""
Else
Chged.Offset(, 12) = Application.UserName
Chged.Offset(, 13) = XlParamaterDataType & " " & Format(Now(), "hh:mm AM/PM")
Chged.Offset(, 14) = XlParamaterDataType & " " & Format(Now(), "m/d/yyyy")
End If
End If
Next
End Sub

Regards,
Dan.

No apologies necessary, I said almost the exact same thing about the Change event a few years ago in a meeting. I feel your pain (times 10, since I was in a clients office) Yes, I take back what I said. I was sure that a procedure changing the value of a cell would fire the event but I checked it with a test form anyway. Unfortunately, I had the userform put a value in cell A1 which meant that the code following 'If (Chged.Row > 1) Then' was not executed and I made a booboo. Again, my apologies Daniel. : Bit of misinformation there. : The change event is triggered whenever a cell value is changed, even if the change is triggered by a userform. : However, I do agree with your comment about putting the code on the userform.

Hi Kevin,

You are right--the worksheet's change event is not triggered when a cell is changed from a userform's event code. The way around this is to simply put the code that adds the date/time/username stamps directly into the appropriate userform event time routine. For example, say that you have the following event routine now for a textbox on your userform:

Private Sub TextBox1_AfterUpdate()
Range("a5") = TextBox1.Value
End Sub

The following change would be the equivalent of having your worksheet's change event run:

Private Sub TextBox1_AfterUpdate()
Set Chged = [a5]
Chged = TextBox1.Value
' then your event date/time stamp code:
If (Chged.Column = 1) Then
If (Chged.Row > 1) Then
If (Chged = "") Then
Chged.Offset(, 1) = ""
Else
Chged.Offset(, 13) = Application.UserName
Chged.Offset(, 14) = XlParamaterDataType & " " & Format(Now(), "hh:mm AM/PM")
Chged.Offset(, 15) = XlParamaterDataType & " " & Format(Now(), "m/d/yyyy")
End If
End If
End If
End Sub

I hope this helps.

Damon

Re: Any other ideas

That's a problem with programming triggered by the Change event.

You've got a solution then?

Re: Any other ideas

Re: Any other ideas

Are you selecting a range before activating the form? Target is the actual cells that are selected, so if you don't tell Excel what range you're writing to, I don't think your macro will work. : Again, it is working great if the data entry is done directly into the worksheet, but if entered from a form, unless you actually double click on the data entered in column A, it does not recognize that the data is there, so it does not work with the form. Am I missing something?

Re: Any other ideas

You've got a solution then?

The solution was posted. I changed the code so it only worked if a cell in column A was changed.

Re: Not a "designed form"

:Yes, sorry I should have mentioned that I was working in Excel 97, it is the selection "data - form" it is not a custom form, I am sure that probably makes a difference. Are you selecting a range before activating the form? Target is the actual cells that are selected, so if you don't tell Excel what range you're writing to, I don't think your macro will work.

Re: Not a "designed form"

OK got you now.

Yep you're screwed.

Dataforms basically freeze the ability to run macros in much the same as trying to run a macro when the cursor is still in a cell that you are manually editing. :Yes, sorry I should have mentioned that I was working in Excel 97, it is the selection "data - form" it is not a custom form, I am sure that probably makes a difference. : Are you selecting a range before activating the form? Target is the actual cells that are selected, so if you don't tell Excel what range you're writing to, I don't think your macro will work.