moogthemoog

Board Regular
Joined
Nov 17, 2004
Messages
51
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
I maybe off at a tangent, but if it is code doing changes there is no undo function for run code (I believe)
 

moogthemoog

Board Regular
Joined
Nov 17, 2004
Messages
51
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

ADVERTISEMENT

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
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Board Regular
Joined
Nov 17, 2004
Messages
51
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,767
Members
412,617
Latest member
mlharris
Top