"Undo" stops working when using VB

[SigalBL]

Hi

I have an excel (365) with code in VB.

After I run the module the "undo" stops working, when I remove the VB module the "undo" acts normaly.

What can I do to solve this?

Can the VB code cause this? (I can add it to here) the code copies the comments with the Vlookup function. 

Thanks for your help

Sigal

[#2]

Yes, many VBA commands clear the Undo stack in Excel (any VBA command that modifies something on your spreadsheet). There is nothing you can change about that, other than:
- Not using that VBA command
- Looking for an alternative method

- Use VBA to build our own undo
To be able to advise we need to see your code.

[#3]

Hi

Thanks for your answer, If there is another way to do it i'll be happy to learn.

I need the function to do: when I use Vlookup I need to copy not only the value of the relevant cell but also the comment of that cell.

To do so I found a VBA code in the web that I use.

I will appreciate any workaround to fix it, the function runs only when called (not calculated all the time).

Following the code I use:

Function VlookupComment(myVal As Variant, myTable As Range, _
myColumn As Long, myBoolean As Boolean) As Variant

Application.Volatile True

Dim res As Variant 'could be an error
Dim myLookupCell As Range

res = Application.Match(myVal, myTable.Columns(1), myBoolean)
If IsError(res) Then
VlookupComment = 0
Else
Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
VlookupComment = myLookupCell.Value
With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If

If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function

Thanks

Sigal

[#4]

To be honest, I would redesign the spreadsheet and move the cell-comments to a separate cell where the comment is entered in the cell as a value so you can use VLOOKUP as designed.