How to reverse a vba macro...

[BettyBoop0916]

Hi there,

I have a vba, that when a certain phrase is selected from a drop-down list in column A, a macro is triggered. My only issue is if I make a mistake, and I select that phrase by accident, how do I reverse the macro without having to unlock the sheet, and deleting the cells.

This is my vba code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 And Target.Value = "(5675) Gas & Oil - Regina" Then
Application.EnableEvents = False
Call GasAndOil
Application.EnableEvents = True
End If
End Sub

Can someone help with this?

 

[RSXchin]

nothing can be done if GasAndOil deletes anything... but if it's only formatting and locking, then we can help you. We just need to see that code.

 

[wigi]

- you could insert a MsgBox asking whether you are sure or not (cancel in that case). It will give you more work though.

- how often does the mistake happen / how likely is it to occur?

- how to reverse the macro is impossible to say, since we do not see what happens in the GasAndOil macro.

 

[BettyBoop0916]

This is what happens in the macro:

Sub GasAndOil()
ActiveSheet.Unprotect
Rows(ActiveCell.Row & ":" & ActiveCell.Row + 5).Select
Selection.Delete
Worksheets("Data").Visible = True
Sheets("Data").Select
Sheets("Data").Range("C9:I14").Select
Selection.Copy
Sheets(Index).Select
Selection.Insert
Worksheets("Data").Visible = False
ActiveSheet.Protect
End Sub

So basically, it is pulling cells from a hidden worksheet (data) and inserting them into the current worksheet. I would like to know if there is a way to reverse this, without having a imput box as I would find that pretty tedious.

Thanks!

 

[RSXchin]

Sorry, you deleted Items.... If I may suggest that you add in lines to save the worksheet as a DIFFERENT name. That way, you can revert to an older version.

 

[BettyBoop0916]

Dang...yeah, I'm creating this for someone who is not proficient in excel and would not know necessarily how to unlock a workbook to delete the offending cells.

I do work off of a template already, which is copied and inserted at the end of the workbook. She has, by accident selected the gas and oil profile when not needed, and then didn't know how to fix it.

 

[wigi]

Another question is why do you delete and insert rows? You are only pasting data in the exact same region so there is no need to delete and insert rows.

Change that one first and afterwards try to tackle the problem of unwanted macro firing.

Also, do not .Select, it is not needed.

Educating users is also a good point.

 

[BettyBoop0916]

Thanks guys...I have given her a lesson or two, however she works out of another location two provinces away, so it is quite difficult to do so over the phone.

I have explained to her how to unlock and relock a workbook, but she still has some slight issues.

I guess I'm just going to have to trust her to relock the workbook afterwards...I don't want her tinkering with formulas, which is why I locked the workbook in the first place.

Thanks!

 

[Ruddles]

I have a vba, that when a certain phrase is selected from a drop-down list in column A, a macro is triggered. My only issue is if I make a mistake, and I select that phrase by accident, how do I reverse the macro without having to unlock the sheet, and deleting the cells.

Two solutions spring to mind and I have used them both:-

• As the first step in your macro, save the workbook. If the user wishes to 'undo', they close the workbook without saving and re-open the saved version. Be careful if you do this as the user might not appreciate the workbook being saved without their consent.
• Save anything and everything which the macro changes or deletes and then provide an 'undo' command button for the user to press when they've realise their mistake. The code behind the command button then restores the state of the workbook before the macro ran. If you do this, you should only enable this 'undo' command button for a limited period otherwise you run the risk of your 'restore' code overwriting something the user does later. (This might not be applicable but it's wise to consider the possibility.)