Undo button

[dan2]

I seem to be full of questions today

How do I put a button on my spreadsheet that will do the same function as the undo button on the tool bar?
(the tool bar is hidden from the end user)

Thanks in advance (again)

Dan

[dk]

Hi Dan,

If you make the Control Toolbox visible (View, Toolbars) and choose a Command Button). Draw it onto your sheet where you'd like. Right click it and choose Properties. Set the TakeFocus******* property to False. Then double click the button and use this code:-

Private Sub CommandButton1_Click()
Application.Undo
End Sub

You have to set the TakeFocus******* to false so that the worksheet keeps the focus and the undo method won't fail.

HTH,
Dan

[Tom Schreiner]

Hi
Now it is working...
Sent E-Mail, could not post.
I could be wrong, but the above will only undo the last user interface action.
If you create a custom Toolbar, add the Tools you wish, and then call it by name as visible, you will retain full functionality for that or those tools.
Tom

[dk]

Tom,

You're not wrong. The code I provided would not provide the full functionality of the normal Undo button - it'll only undo the last action carried out by the user before the macro was ran. I believe that Undo stores the last 16 actions. This code is an example of what you suggested in that it creates a commandbar with the fully functional Undo button on it.

Regards,
Dan

Sub AddBar()
Dim comBar As CommandBar

'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0

'Create a floating commandbar with an Undo button on it
Set comBar = Application.CommandBars.Add("MyBar", msoBarFloating)
comBar.Controls.Add ID:=128 '128 is the ID of the Undo button
comBar.Visible = True
End Sub

[dan2]

DK

The command button idea is the one I like for this app. however after testing it I have found 1 problem with it.

The sheets it will be on are part protected and if the user presses the button and there is nothing to undo then it just breaks the code.

Any ideas of how toget round this?

Dan.

[dk]

DK

The command button idea is the one I like for this app. however after testing it I have found 1 problem with it.

The sheets it will be on are part protected and if the user presses the button and there is nothing to undo then it just breaks the code.

Any ideas of how toget round this?

Dan.

Hello again Dan,

The way around this is to include error handling. Try this if you want to let the user know that the undo operation has failed:-

Private Sub CommandButton1_Click()
Err.Clear
On Error Resume Next
Application.Undo
If Err.Number <> 0 Then MsgBox "Cannot undo"
End Sub

or this if you're not worried about them knowing:-

Private Sub CommandButton1_Click()
On Error Resume Next
Application.Undo
End Sub

HTH,
Dan



[ This Message was edited by: dk on 2002-03-31 09:07 ]

[duddm00]

Why make things difficult if there is an easier way...just press Ctrl Z.
If you want to go back a few more steps repeat the action.
Melvin.
PS I dont know offhand how many steps back U can take.

[Jack in the UK]

Hi guys

OK undos.. if nacro run one way trip no undo .. any fix

Or is it business as usula as you program in you also remenber to program out.... if this is needed of cause,,

What im asking is there a UNDO to VBA actions.. like th eundo button.. i never found one...

[Al Chara]

Jack,

I never found an undo for VBA actions, if I am in a case where I know I will need to do an undo action, then I make a copy of the sheet before the macro runs and then my undo button deletes the old copy and replaces it with the copied version. I know its not a very efficient way, but it works.

[dk]

Jack,

You can't undo a macro using normal Undo. What you can do is write a macro which undoes what the first one did and then use Application.OnUndo e.g.

Paste this code into a module and then run macro1. Now click the Undo button and Macro1 will be undone. If you click the dropdown list next to the Undo button you'll see the text Undo Macro1. This is the first argument of the OnUndo method.

HTH,
Dan.

Sub Macro1()
'A very simple demonstration of Application.OnUndo
Range("A1:A10").Formula = "=rand()"
Application.OnUndo "Undo Macro1", "UndoMacro1"
End Sub

Sub UndoMacro1()
Range("A1:A10").ClearContents
End Sub