Undo button

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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 TakeFocusOnClick 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 TakeFocusOnClick to false so that the worksheet keeps the focus and the undo method won't fail.

HTH,
Dan
 
Upvote 0
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
 
Upvote 0
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

Code:
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
 
Upvote 0
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.
 
Upvote 0
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:-

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

Code:
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
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