Undo doesn't work!

[Nimish]

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

 

[Franz Verga]

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

 

[Nimish]

Is there a workaround?

 

[Dave Peterson]

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.

 

[Franz Verga]

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