How to undo code (delete it without causing errors)

[Gingertrees]

I'm nearing completion of a project, and I'm about ready to add the code to force user to enable macros and to disable drag/drop and copy/paste

I have done this in test workbooks, but I keep running into one problem: I cannot undo the code if needbe. For example, if I want to modify the sheet later on I may wish to re-enable the copy/paste functions. But deleting the code or putting apostrophes in front of it causes errors.

How would I disable the code below:
[vba]
Option Explicit

Private Sub Workbook_Open()
'this is located in the ThisWorkbook module
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call UnhideSheets

.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.Goto Worksheets(1).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False

Call HideSheets

.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_Activate()

Dim oCtrl As Office.CommandBarControl



'Disable all Cut menus

For Each oCtrl In Application.CommandBars.FindControls(ID:=21)

oCtrl.Enabled = False

Next oCtrl



'Disable all Copy menus

For Each oCtrl In Application.CommandBars.FindControls(ID:=19)

oCtrl.Enabled = False

Next oCtrl



Application.CellDragAndDrop = False



End Sub



Private Sub Workbook_Deactivate()

Dim oCtrl As Office.CommandBarControl



'Enable all Cut menus

For Each oCtrl In Application.CommandBars.FindControls(ID:=21)

oCtrl.Enabled = True

Next oCtrl



'Enable all Copy menus

For Each oCtrl In Application.CommandBars.FindControls(ID:=19)

oCtrl.Enabled = True

Next oCtrl



Application.CellDragAndDrop = True



End Sub



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

With Application

.CellDragAndDrop = False

.CutCopyMode = False 'Clear clipboard

End With

End Sub
'force macros courtesy VBAX knowledge base, 'http://www.vbaexpress.com/kb/getarticle.php?kb_id=578
'disable copy/paste courtesy Ozgrid,
'http://www.ozgrid.com/VBA/disable-cut-copy.htm
[/vba]

[Artik]

Comment all code before modify and uncomment this after modification.

Artik

[lucas]

Try this: As Artik says comment the code but I would be selective. I would open the workbook, comment the open procedure and the activate procedure and the Workbook_SheetSelectionChange procedure.

note some sheets may be hidden by the workbook close procedure and you might want to comment just that part of the close procedure but I wouldn't comment the whole thing because that is where cut/copy etc. is re-enabled.

Close the workbook and re-open it......make changes

uncomment it and save your changes. Close and re-open......

[GTO]

Greetings Ginger,

If your best guess is that you will only "tune-up" the project a time or two, I would follow Steve's suggestion. Being a little clumsy myself, as well as usually finding out that some user will find a way to goober up the wb in some way I wouldn't have thought of..., I have found that I might keep making changes for a while after putting the wb into operation.

If you believe that you might need to make changes for a while, might I suggest you leave yourself a "developer's backdoor" so-to-speak? I have found this easier later when I wanted to make some quick changes.

In the ThisWorkbook module:
[vba]Private Sub Workbook_Activate()

'// If you have run 'EnableStuff...(), you'll be able to modify workbook as //
'// long as you don't reset. //
If Not bolMyOverride Then
'// Code moved to own sub //
Call CutCopy_Disable
End If
End Sub

Private Sub Workbook_Deactivate()

'// SAA //
If Not bolMyOverride Then
Call CutCopy_Enable
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Not bolMyOverride Then
With Application
.CellDragAndDrop = False
.CutCopyMode = False 'Clear clipboard
End With
End If

End Sub

Private Sub EnableStuffSoICanWork()
Call CutCopy_Enable
bolMyOverride = True
End Sub

Private Sub DisableStuffSoOthersCannotGooberUpMyDay()
Call CutCopy_Disable
bolMyOverride = False
'// Optional of course //
ThisWorkbook.Save
End Sub

Private Sub CutCopy_Disable()
Dim oCtrl As Office.CommandBarControl

'Disable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl

'Disable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl

Application.CellDragAndDrop = False
End Sub

Private Sub CutCopy_Enable()
Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl

'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl

Application.CellDragAndDrop = True
End Sub[/vba]

Note: I left out the opening and closing events and associated subs, as these wouldn't change.

This way you could just use the EnableStuff...() to "unlock" everything while you are adding further development etc., and of course run DisableStuff...() to reprotect...

Hope this helps,

Mark

[Gingertrees]

That looks good, but for one thing: bolMyOverride is never defined. What would be the code to define that? Would it be a separate Sub? Where would this sub be inserted?

[GTO]

That looks good, but for one thing: bolMyOverride is never defined. What would be the code to define that? Would it be a separate Sub? Where would this sub be inserted?

Ack!

Just Dim it at top of ThisWorkbook, should be fine.

Sorry 'bout that,

Mark

[Gingertrees]

"Object variable or With block variable not set" comes up reliably, even with the following code:
(What am I doing wrong?!?!)
~Gingertrees

[vba]
Option Explicit

Dim bolMyOverride As Object

Private Sub Workbook_Open()
'this is located in the ThisWorkbook module
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call UnhideSheets
.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub
'
Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.Goto Worksheets(1).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call HideSheets
.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub Workbook_Activate()
Dim bolMyOverride As Object

If Not bolMyOverride Then
Call CutCopy_Disable
End If
End Sub



Private Sub Workbook_Deactivate()
Dim bolMyOverride As Object

If Not bolMyOverride Then
Call CutCopy_Enable
End If

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim bolMyOverride As Object

If Not bolMyOverride Then
With Application
.CellDragAndDrop = False
.CutCopyMode = False 'Clear clipboard
End With
End If

End Sub
'force macros courtesy VBAX knowledge base, 'http://www.vbaexpress.com/kb/getarticle.php?kb_id=578
'disable copy/paste courtesy Ozgrid,
'http://www.ozgrid.com/VBA/disable-cut-copy.htm

Private Sub EnableStuffSoICanWork()
Dim bolMyOverride As Object
Call CutCopy_Enable
bolMyOverride = True
End Sub

Private Sub DisableStuffSoOthersCannotGooberUpMyDay()
Dim bolMyOverride As Object

Call CutCopy_Disable
bolMyOverride = False
'// Optional of course //
ThisWorkbook.Save
End Sub

Private Sub CutCopy_Disable()
Dim oCtrl As Office.CommandBarControl

'Disable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl

'Disable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl

Application.CellDragAndDrop = False
End Sub

Private Sub CutCopy_Enable()
Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl

'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl

Application.CellDragAndDrop = True
End Sub

[/vba]

[GTO]

That looks good, but for one thing: bolMyOverride is never defined. What would be the code to define that? Would it be a separate Sub? Where would this sub be inserted?

Hi Ginger,

Please accept my apologies, in taking another look at your question, I realize my response was unclear and my explanation poor.

'bolMyOverride' is a Boolean. Thus - in 'EnableStuffSoICanWork', we first called CutCopy_Enable (so that everything was re-enabled) and we then set the flag (bolMyOverride) to True.
[vba]Private Sub EnableStuffSoICanWork()
Call CutCopy_Enable
bolMyOverride = True
End Sub[/vba]

Now after you 'manually' (set the cursor someplace in the above procedure and click F5) run 'EnableStuffSoICanWork()', the flag/Boolean remains TRUE until you reset or until you run 'DisableStuffSoOthersCannotGooberUpMyDay()'.

I should mention here that I think that part of the reason I seemed to have thrown you "off track" was simply the style or manner in which I wrote the statement/test. If that is the case, here's a hopefully better explanation of that.
The statements:
[vba]If Not bolMyOverride Then[/vba]
or
[vba]If Not bolMyOverride = True Then[/vba]
or
[vba]If bolMyOverride <> True Then[/vba]

...are all effectively the same statement. In short - the "= True" part is not actually necessary for Excel to "understand" the test. Does that make better sense?

So when I said to Dim it up at the top of the module (again, sorry for my lacking clarity) what I meant was like this (presuming I have copied/pasted stuff correctly):

[vba]' MODULE: ThisWorkbook
Option Explicit

Dim bolMyOverride As Boolean

'// BeforeClose and Open remain as you had them, as do the proedures 'HideSheets' and //
'// 'UnhideSheets'. //
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call HideSheets
.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub Workbook_Open()
'this is located in the ThisWorkbook module
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call UnhideSheets
.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With

End Sub


Private Sub Workbook_Activate()

'// After you have run 'EnableStuffSoICanWork()', then the Boolean 'bolMyOverride' //
'// equals TRUE. //

'// So... assuming you've run the aforementioned sub and bolMyOverride has been set //
'// to True, the below test fails, and 'CutCopy_Disable' is never called. In short,//
'// as long as bolMyOverride retains a value of True, you can make mods w/o //
'// interference, as long as you don't reset. //
If Not bolMyOverride Then
'// Code moved to own sub //
Call CutCopy_Disable
End If
End Sub

Private Sub Workbook_Deactivate()

'// SAA //
If Not bolMyOverride Then
Call CutCopy_Enable
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Not bolMyOverride Then
With Application
.CellDragAndDrop = False
.CutCopyMode = False 'Clear clipboard
End With
End If

End Sub


Private Sub EnableStuffSoICanWork()
Call CutCopy_Enable
bolMyOverride = True
End Sub

Private Sub DisableStuffSoOthersCannotGooberUpMyDay()
Call CutCopy_Disable
bolMyOverride = False
'// Optional of course //
ThisWorkbook.Save
End Sub

Private Sub CutCopy_Disable()
Dim oCtrl As Office.CommandBarControl

'Disable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl

'Disable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl

Application.CellDragAndDrop = False
End Sub

Private Sub CutCopy_Enable()
Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl

'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl

Application.CellDragAndDrop = True
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.Goto Worksheets(1).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub[/vba]

Now, as I mentioned before, the flag will remain True until you reset, run 'DisableStuffSoOthersCannotGooberUpMyDay()', or of course close and reopen the workbook. The reason I mentioned that was mostly at that little glitch potential of when you reset. If you are like me and don't write the "perfect" code on the first try everytime, of course that nasty little bugger of a dialog pops up once in a while during testing that notifies you of an error and gives you the choice to End or Debug. Now assuming you debug first while locating whatever gobbered up, you porbably then End the code, rewrite, and retest. As you have reset, you would need to first re-run 'EnableStuffSoICanWork()' to again set the flag to True.

Well hopefully I have given a better explanation and have us "on the same page" so-to-speak. As I was thinking about this though, it occurred that you might like another way better, so please take a quick look-see at this:

[vba]' MODULE: ThisWorkbook
Option Explicit

'// Using a Constant instead of a variable, change the below to True while you are //
'// testing. Remember to change back to False and resave the workbook when all done. //
Const MyOverride_BOL As Boolean = False
'
'// BeforeClose and Open remain as you had them, as do the proedures 'HideSheets' and //
'// 'UnhideSheets'. //
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call HideSheets
.ScreenUpdating = True
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub Workbook_Open()
'this is located in the ThisWorkbook module
With Application
'disable the ESC key
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
Call UnhideSheets
.ScreenUpdating = True
're-enable ESC key
.EnableCancelKey = xlInterrupt
End With
End Sub

Private Sub Workbook_Activate()

'// After you have run 'EnableStuffSoICanWork()' AND changed the Constant //
'// 'MyOverride_BOL' to True (up at top of Module), then you can make modifications //
'// without CutCopy_DIsable ever being called. Just remember to change //
'// 'MyOverride_BOL' back to False and Save the workbook after you are all done. //

If Not MyOverride_BOL Then
'// Code moved to own sub //
Call CutCopy_Disable
End If
End Sub

Private Sub Workbook_Deactivate()

'// SAA //
If Not MyOverride_BOL Then
Call CutCopy_Enable
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Not MyOverride_BOL Then
With Application
.CellDragAndDrop = False
.CutCopyMode = False 'Clear clipboard
End With
End If

End Sub


Private Sub EnableStuffSoICanWork()
Call CutCopy_Enable
End Sub

Private Sub DisableStuffSoOthersCannotGooberUpMyDay()
Call CutCopy_Disable
'// Optional of course //
ThisWorkbook.Save
End Sub

Private Sub CutCopy_Disable()
Dim oCtrl As Office.CommandBarControl

'Disable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl

'Disable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl

Application.CellDragAndDrop = False
End Sub

Private Sub CutCopy_Enable()
Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl

'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl

Application.CellDragAndDrop = True
End Sub

Private Sub HideSheets()
'
Dim Sheet As Object '< Includes worksheets and chartsheets
'
With Sheets("Prompt")
'
'the hiding of the sheets constitutes a change that generates
'an automatic "Save?" prompt, so IF the book has already
'been saved prior to this point, the next line and the lines
'relating to .[A100] below bypass the "Save?" dialog...
If ThisWorkbook.Saved = True Then .[A100] = "Saved"
'
.Visible = xlSheetVisible
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
'
If .[A100] = "Saved" Then
.[A100].ClearContents
ThisWorkbook.Save
End If
'
Set Sheet = Nothing
End With
'
End Sub

Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.Goto Worksheets(1).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub[/vba]

Now of course the above is just the same thing, except that during a development session, you don't need to worry about running anything again each time you need to reset. Of course the downside is you must remember to change the constant bacl to False and resave the workbook when you're all finished.

I hope this helps, and if I remained unclear in any part, please certainly say so and I'll try to answer better or use less "Markeneese" as my friends sometimes bash me for...

Mark

Edit: I forgot to mention that I didn't test the second way (using a Constant), but I don't think I goobered anything in it.