I am new to the macro world and need some help.

I am trying to set a macro that will place a comment. The information in the
comment should be:

User name. Datestamp (fixed to when macro was applied).

The comment should not be modifiable.

Is this possible?


(The object is to have this macro act as a "signature" for certain users
showing that they have "signed off" on information in specific cells.
Non-legal, intra-office use only.)

If there is an easier way than the macro suggestion above, I am open to
suggestions.

Thank you very much.

Look at this for an example of a time and date stamp:
http://www.mcgimpsey.com/excel/timestamp.html

You can protect your cells, with a password:
http://www.uwgb.edu/compserv/ehelp/excel/protect.htm

Now to modify the cells, you will have to unprotect the range, do the
modification, and then re-protect the range. This id doable in code; not to
difficult wither. Let's take one step at a time though. Get the first part
working to your satisfaction, and then post again if you have more questions
about how to unprotect a range and then re-protect a range with VAB. Maybe
you can figure it out on your own without even re-posting. :)

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Adam at Star Packaging" wrote:

I am new to the macro world and need some help.

I am trying to set a macro that will place a comment. The information in the
comment should be:

User name. Datestamp (fixed to when macro was applied).

The comment should not be modifiable.

Is this possible?


(The object is to have this macro act as a "signature" for certain users
showing that they have "signed off" on information in specific cells.
Non-legal, intra-office use only.)

If there is an easier way than the macro suggestion above, I am open to
suggestions.

Thank you very much.

Will this work for you?
'Summary: Place this code in the Worksheet_Change() event procedure
' Every time a cell's value is changed the date, time , old value,
' new value, and the user are recorded in a comment.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewText As String
Dim NewVal As Variant
Dim OldText As String
Dim OldVal As Variant

Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = ActiveCell.Value
ActiveCell = NewVal
Application.EnableEvents = True

NewText = "On " & Now() & " cell changed from " & OldVal _
& " to " & NewVal & " by " & Environ("UserName")

If ActiveCell.Comment Is Nothing Then
ActiveCell.AddComment
End If

With ActiveCell.Comment
..Shape.TextFrame.AutoSize = True
OldText = .Text & vbLf
..Text Text:=OldText & NewText
End With

End Sub

Also, see this:
http://www.contextures.com/xlcomments03.html#Plain


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Adam at Star Packaging" wrote:

I am new to the macro world and need some help.

I am trying to set a macro that will place a comment. The information in the
comment should be:

User name. Datestamp (fixed to when macro was applied).

The comment should not be modifiable.

Is this possible?


(The object is to have this macro act as a "signature" for certain users
showing that they have "signed off" on information in specific cells.
Non-legal, intra-office use only.)

If there is an easier way than the macro suggestion above, I am open to
suggestions.

Thank you very much.

Thank you very much for the reply, I really appreciate your time.

Let me give a little more background...

We have a worksheet, that is already protected to keep certain cells and
changes from happening. So we are trying to find a way to do this without
having to unprotect the entire sheet every time certain cells are
updated/signed off on.


Based on my very limited knowledge of the code you posted, it looks like it
will contain the information we need. (looks much like the "track changes"
info which is fine.)

However I'm having trouble putting this into the macro creator.

When you said "Place this code in the Worksheet_Change() event procedure...
I'm not quite sure what you mean.

-Adam

"ryguy7272" wrote:

Will this work for you?
'Summary: Place this code in the Worksheet_Change() event procedure
' Every time a cell's value is changed the date, time , old value,
' new value, and the user are recorded in a comment.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewText As String
Dim NewVal As Variant
Dim OldText As String
Dim OldVal As Variant

Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = ActiveCell.Value
ActiveCell = NewVal
Application.EnableEvents = True

NewText = "On " & Now() & " cell changed from " & OldVal _
& " to " & NewVal & " by " & Environ("UserName")

If ActiveCell.Comment Is Nothing Then
ActiveCell.AddComment
End If

With ActiveCell.Comment
.Shape.TextFrame.AutoSize = True
OldText = .Text & vbLf
.Text Text:=OldText & NewText
End With

End Sub

Also, see this:
http://www.contextures.com/xlcomments03.html#Plain


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Adam at Star Packaging" wrote:

I am new to the macro world and need some help.

I am trying to set a macro that will place a comment. The information in the
comment should be:

User name. Datestamp (fixed to when macro was applied).

The comment should not be modifiable.

Is this possible?


(The object is to have this macro act as a "signature" for certain users
showing that they have "signed off" on information in specific cells.
Non-legal, intra-office use only.)

If there is an easier way than the macro suggestion above, I am open to
suggestions.

Thank you very much.

read the info he
http://www.ozgrid.com/VBA/run-macros-change.htm

Also, read this:
http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm
that's for the whole sheet, but you sadi you may want to lock only specific
cells and ranges in a protected worksheet, right. You can do that too. It's
not hard. I'd say look on the web for some examples of what you need to do.

I haven't seen your code, so I can only make a guess here...
1) Unprotect the appropriate range
2) Perform the operation (I gave you the code earlier today)
3) Reprotect the appropriate range

If it is more involved than that you have to tell me.

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Adam at Star Packaging" wrote:

Thank you very much for the reply, I really appreciate your time.

Let me give a little more background...

We have a worksheet, that is already protected to keep certain cells and
changes from happening. So we are trying to find a way to do this without
having to unprotect the entire sheet every time certain cells are
updated/signed off on.


Based on my very limited knowledge of the code you posted, it looks like it
will contain the information we need. (looks much like the "track changes"
info which is fine.)

However I'm having trouble putting this into the macro creator.

When you said "Place this code in the Worksheet_Change() event procedure...
I'm not quite sure what you mean.

-Adam

"ryguy7272" wrote:

Will this work for you?
'Summary: Place this code in the Worksheet_Change() event procedure
' Every time a cell's value is changed the date, time , old value,
' new value, and the user are recorded in a comment.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewText As String
Dim NewVal As Variant
Dim OldText As String
Dim OldVal As Variant

Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = ActiveCell.Value
ActiveCell = NewVal
Application.EnableEvents = True

NewText = "On " & Now() & " cell changed from " & OldVal _
& " to " & NewVal & " by " & Environ("UserName")

If ActiveCell.Comment Is Nothing Then
ActiveCell.AddComment
End If

With ActiveCell.Comment
.Shape.TextFrame.AutoSize = True
OldText = .Text & vbLf
.Text Text:=OldText & NewText
End With

End Sub

Also, see this:
http://www.contextures.com/xlcomments03.html#Plain


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Adam at Star Packaging" wrote:

I am new to the macro world and need some help.

I am trying to set a macro that will place a comment. The information in the
comment should be:

User name. Datestamp (fixed to when macro was applied).

The comment should not be modifiable.

Is this possible?


(The object is to have this macro act as a "signature" for certain users
showing that they have "signed off" on information in specific cells.
Non-legal, intra-office use only.)

If there is an easier way than the macro suggestion above, I am open to
suggestions.

Thank you very much.

I found where to enter the code, but now I can't make it actually work. I'm
not sure if it's because I'm on Excel 2007 and the verbage is a little bit
different or if I'm just in way in over my head.

I read through those pages you posted, and I couldn't even get the simple
one to run.:

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "You just changed " & Target.Address

End Sub

After you enter a code like this...how do you get it to "run"

(On previous attempts with comment macros, I would just hit the run button
and it would work. Now nothing happens.)


"ryguy7272" wrote:

Will this work for you?
'Summary: Place this code in the Worksheet_Change() event procedure
' Every time a cell's value is changed the date, time , old value,
' new value, and the user are recorded in a comment.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewText As String
Dim NewVal As Variant
Dim OldText As String
Dim OldVal As Variant

Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = ActiveCell.Value
ActiveCell = NewVal
Application.EnableEvents = True

NewText = "On " & Now() & " cell changed from " & OldVal _
& " to " & NewVal & " by " & Environ("UserName")

If ActiveCell.Comment Is Nothing Then
ActiveCell.AddComment
End If

With ActiveCell.Comment
.Shape.TextFrame.AutoSize = True
OldText = .Text & vbLf
.Text Text:=OldText & NewText
End With

End Sub

Also, see this:
http://www.contextures.com/xlcomments03.html#Plain


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Adam at Star Packaging" wrote:

I am new to the macro world and need some help.

I am trying to set a macro that will place a comment. The information in the
comment should be:

User name. Datestamp (fixed to when macro was applied).

The comment should not be modifiable.

Is this possible?


(The object is to have this macro act as a "signature" for certain users
showing that they have "signed off" on information in specific cells.
Non-legal, intra-office use only.)

If there is an easier way than the macro suggestion above, I am open to
suggestions.

Thank you very much.