Home

"Undo" stops working when using VB

%3CLINGO-SUB%20id%3D%22lingo-sub-1103677%22%20slang%3D%22en-US%22%3E%22Undo%22%20stops%20working%20when%20using%20VB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1103677%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20(365)%20with%20code%20in%20VB.%3C%2FP%3E%3CP%3EAfter%20I%20run%20the%20module%20the%20%22undo%22%20stops%20working%2C%20when%20I%20remove%20the%20VB%20module%20the%20%22undo%22%20acts%20normaly.%3C%2FP%3E%3CP%3EWhat%20can%20I%20do%20to%20solve%20this%3F%3C%2FP%3E%3CP%3ECan%20the%20VB%20code%20cause%20this%3F%20(I%20can%20add%20it%20to%20here)%20the%20code%20copies%20the%20comments%20with%20the%20Vlookup%20function.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%3C%2FP%3E%3CP%3ESigal%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1103677%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1105112%22%20slang%3D%22en-US%22%3ERe%3A%20%22Undo%22%20stops%20working%20when%20using%20VB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1105112%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20many%20VBA%20commands%20clear%20the%20Undo%20stack%20in%20Excel%20(any%20VBA%20command%20that%20modifies%20something%20on%20your%20spreadsheet).%20There%20is%20nothing%20you%20can%20change%20about%20that%2C%20other%20than%3A%3CBR%20%2F%3E-%20Not%20using%20that%20VBA%20command%3CBR%20%2F%3E-%20Looking%20for%20an%20alternative%20method%3C%2FP%3E%0A%3CP%3E-%20Use%20VBA%20to%20build%20our%20own%20undo%3CBR%20%2F%3ETo%20be%20able%20to%20advise%20we%20need%20to%20see%20your%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1106006%22%20slang%3D%22en-US%22%3ERe%3A%20%22Undo%22%20stops%20working%20when%20using%20VB%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1106006%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThanks%20for%20your%20answer%2C%20If%20there%20is%20another%20way%20to%20do%20it%20i'll%20be%20happy%20to%20learn.%3C%2FP%3E%3CP%3EI%20need%20the%20function%20to%20do%3A%20when%20I%20use%20Vlookup%20I%20need%20to%20copy%20not%20only%20the%20value%20of%20the%20relevant%20cell%20but%20also%20the%20comment%20of%20that%20cell.%3C%2FP%3E%3CP%3ETo%20do%20so%20I%20found%20a%20VBA%20code%20in%20the%20web%20that%20I%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20appreciate%20any%20workaround%20to%20fix%20it%2C%20the%20function%20runs%20only%20when%20called%20(not%20calculated%20all%20the%20time).%3C%2FP%3E%3CP%3EFollowing%20the%20code%20I%20use%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CFONT%3EFunction%20VlookupComment(myVal%20As%20Variant%2C%20myTable%20As%20Range%2C%20_%3CBR%20%2F%3EmyColumn%20As%20Long%2C%20myBoolean%20As%20Boolean)%20As%20Variant%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EApplication.Volatile%20True%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EDim%20res%20As%20Variant%20'could%20be%20an%20error%3CBR%20%2F%3EDim%20myLookupCell%20As%20Range%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3Eres%20%3D%20Application.Match(myVal%2C%20myTable.Columns(1)%2C%20myBoolean)%3CBR%20%2F%3EIf%20IsError(res)%20Then%3CBR%20%2F%3EVlookupComment%20%3D%200%3CBR%20%2F%3EElse%3CBR%20%2F%3ESet%20myLookupCell%20%3D%20myTable.Columns(myColumn).Cells(1)(res)%3CBR%20%2F%3EVlookupComment%20%3D%20myLookupCell.Value%3CBR%20%2F%3EWith%20Application.Caller%3CBR%20%2F%3EIf%20.Comment%20Is%20Nothing%20Then%3CBR%20%2F%3E'do%20nothing%3CBR%20%2F%3EElse%3CBR%20%2F%3E.Comment.Delete%3CBR%20%2F%3EEnd%20If%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EIf%20myLookupCell.Comment%20Is%20Nothing%20Then%3CBR%20%2F%3E'no%20comment%2C%20do%20nothing%3CBR%20%2F%3EElse%3CBR%20%2F%3E.AddComment%20Text%3A%3DmyLookupCell.Comment.Text%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EEnd%20If%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3E%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ESigal%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
SigalBL
New Contributor

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

 

3 Replies
Highlighted

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.

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

 

Highlighted
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.