VBA code also clears clipboard

[moogthemoog]

Hi

I have some VBA code that basically checks if there's an error message in cell M58, then display a Command Button "ClearError1"

Private Sub Worksheet_Calculate()

On Error Resume Next
If IsError(Range("M58").Value) Then ClearError1.Visible = True 'Else ClearError1.Visible = False

End Sub

(note that this code is in the individual sheet, not ThisWorkbook)

If ClearError1 button is clicked, it runs some code to clear the error:

Private Sub ClearError1_Click()
Call GetSheetNames
End Sub

In Excel 2007, the Worksheet_Calculate code had the unintended effect of not keeping Copied items in the clipboard (i.e. I could copy and paste once and the clipboard was cleared). This was a bit of a pain, but not the end of the world.
However, in Excel 2010, the code is now having the extra effect of clearing the Undo button (i.e. Undo is not available within Excel). This is a major pain to productivity.

Is there any code that can stop this, or is there an alternate code to displaying or not displaying a Command button which doesn't clear out the Clipboard/Undo memory?

Or, is there an alternative to the Command Button?

Thanks
Jon

 

[mole999]

I maybe off at a tangent, but if it is code doing changes there is no undo function for run code (I believe)

 

[moogthemoog]

I maybe off at a tangent, but if it is code doing changes there is no undo function for run code (I believe)

Agreed, but if the code was altered to say

MsgBox "ERROR" Else MsgBox "No Error"

instead of CommandButton.Visible = True etc, then the Undo function does work?

It's the undo function of text actually entered on the spreadsheet that I'm needing, not to undo the code itself.

 

[Fishboy]

I maybe off at a tangent, but if it is code doing changes there is no undo function for run code (I believe)

This.

Not only does the undo function not exist for changes being made by code, but it also clears the Undo history meaning the last manual change to the document can also not be undone. This is certainly my (painful) experience with Excel 2010 anyway.

 

[Fishboy]

Agreed, but if the code was altered to say

MsgBox "ERROR" Else MsgBox "No Error"

instead of CommandButton.Visible = True etc, then the Undo function does work?

It's the undo function of text actually entered on the spreadsheet that I'm needing, not to undo the code itself.

That should retain the workbook's Undo function yes.

 

[moogthemoog]

That is a shame. Looks like I'll have to do away with the Command button then.

 

[mole999]

I put this into one file

on ThisWorkbook

Private Sub Workbook_Open()
    On Error Resume Next
    'Sheets("risk register").Range("C2").Select
    'ActiveWindow.FreezePanes = True
    Sheets("risk register").Range("C2").FreezePanes = True
    Sheets("tracker").Visible = xlVeryHidden
    Dim Lastrow As Long
    Lastrow = Sheets("tracker").Range("A100000").End(xlUp).Row
    Lastrow = Lastrow + 1
    With Sheets("tracker")
        .Range("A" & Lastrow) = Now()
        .Range("B" & Lastrow) = Environ("USERNAME")
        .Range("C" & Lastrow) = Environ("COMPUTERNAME")
        .Range("D" & Lastrow) = Environ("LOGONSERVER")
        .Range("E" & Lastrow) = Environ("USERDNSDOMAIN")
        .Range("F" & Lastrow) = "=TRIM(RIGHT(SUBSTITUTE(TRIM('Risk Register'!R1), "" "", REPT("" "", LEN(TRIM('Risk Register'!R1)))), LEN(TRIM('Risk Register'!R1))))"
    End With
End Sub

on the target sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Lastrow As Long
    Dim vOldVal
    Application.EnableEvents = False
    Application.Undo
    vOldVal = Target.Value
    Application.Undo
    Application.EnableEvents = True
    On Error Resume Next
    Lastrow = Sheets("tracker").Range("A100000").End(xlUp).Row + 1
    ActiveWorkbook.Sheets("Tracker").Cells(Lastrow, 1) = "Data Input " & ActiveCell.Address & " changed to: " & Target.Value
    ActiveWorkbook.Sheets("Tracker").Cells(Lastrow + 1, 1) = "Data Change " & ActiveCell.Address & " changed from: " & vOldVal
End Sub

not tried changing the sheet with code, but any manual change back and forward is reported there

 

[moogthemoog]

Just been reworking this, and the problem line of code is

ClearError1.Visible = False

Making the ComandButton visible does not affect the Undo text functionality within Excel (as opposed to Undo of the code). But making it invisible does.
Enabling or Disabling the ComandButton also does not affect the Undo memory.

However, the ComandButton then remains visible on the spreadsheet.

Is there another way of hiding the ComandButton, that would still allow me to display it according to conditions in the code?