VBA code also clears clipboard

moogthemoog

New Member
Joined
Nov 17, 2004
Messages
49
Hi

I have some VBA code that basically checks if there's an error message in cell M58, then display a Command Button "ClearError1"
Code:
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:
Code:
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

Moderator
Joined
Oct 23, 2004
Messages
9,899
I maybe off at a tangent, but if it is code doing changes there is no undo function for run code (I believe)
 

moogthemoog

New Member
Joined
Nov 17, 2004
Messages
49
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
Code:
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
Agreed, but if the code was altered to say
Code:
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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
I put this into one file

on ThisWorkbook
Code:
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
Code:
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

New Member
Joined
Nov 17, 2004
Messages
49
Just been reworking this, and the problem line of code is

Code:
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?
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top