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
 

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)
I maybe off at a tangent, but if it is code doing changes there is no undo function for run code (I believe)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top