CODEWITHSUNDEEP
vbaexcel
sady
sady

Reputation: 301

VBA code conflict

I know, I am asking an unusual question. But, please do help me.

I have a below code on Workbook that will take care of copy/paste data on sheets. It would allow me to paste data into the cells without changing format(past only values).

Basically, the code will use destination formatting. similar to "paste values". It would allow the user to paste data from any other format. So that format is consistent across sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim vNewValues as Variant
    NewValues = Target
    Application.EnableEvents = False
    Application.Undo
    Target = NewValues
    Application.EnableEvents = True
End Sub

Along with above code, I also have another code on the sheet that will help me to clear the contents and code is linked to a button. So, when the button is pressed it will clear the contents of the sheet.

Private Sub ResetKey_Click()
If MsgBox("Content of the sheet will be deleted and cannot be restored", vbOKCancel + vbInformation) = vbOK Then
    Worksheets("User").Range("E19:I3018").ClearContents
Else
    Exit Sub
End If
End Sub

Concern: I see a conflict between these codes. Because, when I click on the button I get the error that will point me to Application.Undo in the first code. I tried debugging the code but I was not able to get both to work. Please Suggest.

enter image description here

Upvotes: 2

Views: 374

Answers (2)

user1016274
user1016274

Reputation: 4209

This will work: 

Private Sub ResetKey_Click()
    If MsgBox("Content of the sheet will be deleted and cannot be restored", vbOKCancel + vbInformation) = vbOK Then
        Application.EnableEvents = False
        Worksheets("User").Range("E19:I3018").ClearContents
        Application.EnableEvents = True
    Else
        Exit Sub
    End If
End Sub

That is, you have to suppress the Change event in other macros working on that sheet. Not elegant but doable.

To clarify what the first macro does: it saves the cell's content, undoes a user's paste or input, and then only fills in the value which was pasted, leaving the format intact. The problem with this approach is that the event handler does not return information on the action that triggered it - it could be a paste but clearing cells as well.

Upvotes: 1

QHarr
QHarr

Reputation: 84465

You can only use .Undo to undo the last action in the worksheet not to undo vba actions and must be the first line in the macro. As explained in the documentation.Application.Undo. Quote below:

This method undoes only the last action taken by the user before running the macro, and it must be the first line in the macro. It cannot be used to undo Visual Basic commands.

Upvotes: 1

Related Questions