Undo doesn't work!

I have this worksheet change event, but I think it has disabled the
undo function!

The code below adds a comment to any changed cell, and includes the
Windows User Name of the person changing the cell.

Option Explicit
' This is used by GetUserName() to find the current user's
' name from the API
Private Declare Function Get_User_Name Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long
Function GetUserName() As String
Dim lpBuff As String * 25

Get_User_Name lpBuff, 25
GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment <> "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
GetUserName() & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
'comment perhaps should be resized
End Sub

Nimish wrote:
> I have this worksheet change event, but I think it has disabled the
> undo function!
>
> The code below adds a comment to any changed cell, and includes the
> Windows User Name of the person changing the cell.
>
> Option Explicit
> ' This is used by GetUserName() to find the current user's
> ' name from the API
> Private Declare Function Get_User_Name Lib "advapi32.dll" Alias _
> "GetUserNameA" (ByVal lpBuffer As String, _
> nSize As Long) As Long
> Function GetUserName() As String
> Dim lpBuff As String * 25
>
> Get_User_Name lpBuff, 25
> GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
> End Function
> Private Sub Worksheet_Change(ByVal Target As Range)
> Target.Interior.ColorIndex = 0
> On Error Resume Next
> Dim curComment As String
> curComment = ""
> curComment = Target.Comment.Text
> If curComment <> "" Then curComment = curComment & Chr(10)
> Target.AddComment
> Target.Comment.Text Text:=curComment & _
> GetUserName() & _
> Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
> Format(Time, "hh:mm")
> 'comment perhaps should be resized
> End Sub

Every time you run a macro, history of undo is cleared, so every time you
run a macro you can't undo.

So if you run a macro for every change to your worksheet, you will not to be
able to undo...



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Is there a workaround?

Lots of macros clear the undo/redo stack.

Yours is one of those.

The choice is keep undo and discard the event--or keep the event and live with
undo being gone.

Nimish wrote:
>
> I have this worksheet change event, but I think it has disabled the
> undo function!
>
> The code below adds a comment to any changed cell, and includes the
> Windows User Name of the person changing the cell.
>
> Option Explicit
> ' This is used by GetUserName() to find the current user's
> ' name from the API
> Private Declare Function Get_User_Name Lib "advapi32.dll" Alias _
> "GetUserNameA" (ByVal lpBuffer As String, _
> nSize As Long) As Long
> Function GetUserName() As String
> Dim lpBuff As String * 25
>
> Get_User_Name lpBuff, 25
> GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
> End Function
> Private Sub Worksheet_Change(ByVal Target As Range)
> Target.Interior.ColorIndex = 0
> On Error Resume Next
> Dim curComment As String
> curComment = ""
> curComment = Target.Comment.Text
> If curComment <> "" Then curComment = curComment & Chr(10)
> Target.AddComment
> Target.Comment.Text Text:=curComment & _
> GetUserName() & _
> Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
> Format(Time, "hh:mm")
> 'comment perhaps should be resized
> End Sub

--

Dave Peterson

Nimish wrote:
> Is there a workaround?


Sorry, but AFAIK there's no simple way...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy