Re-Activating OnSave Macro

[KevinPhilips]

Hi there, I got overhanded a VBA project that containts a Workbook_BeforeSave Sub that prevents the file from being saved! When I started working on this I wanted to change stuff, therefore I just put the whole Sub into Comments. Now that I am done with everything, I wanted to take out the comments -> and realised after trying to save this step, how stupid I am... Does anyone have an idea how to fix this mess now? I would really appreciate it. Thanks in advance, Kevin

[KevinPhilips]

OK, just to be clear - this is the code (sorry for German comments):



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'ShowAll

If Range("speicherswitch") = "Kommentar" Then '"speicherswitch" ist eine benannte Zelle im Tabellenblatt "Index"
If Range("ResetAfterSave") <> 1 Then '"ResetAfterSave" in "total" ... wenn die beiden Bedingungen erfüllt
Cancel = True 'dann wird speichern abgebrochen
Else
Range("ResetAfterSave").Value = vbNullString 'mache C1 in "total" leer
Tabelle2.Visible = False '''xlSheetVeryHidden 'Tabelle Index ausblenden
Tabelle3.Visible = False '''xlSheetVeryHidden 'Tabelle Rohdaten ausblenden
End If
End If

If Range("speicherswitch") = "Datei" Then 'Wenn dort Datei steht
'''Modul3.Comspeichern

Application.EnableEvents = False 'Eventmakros werden ausgestellt

'''Technische Info aktuallisieren
If Range("InDBAktual") = 1 Then 'Blatt "Index" C27 steht dort eine 1?
inDBAktuallisieren 'ruft im Modul7 auf
Range("InDBAktual").Value = 0
End If

If Range("ResetAfterSave") = 1 Then 'steht in "total" C1 eine 1?
Sheets("total").Range("A4:BB30000").ClearContents 'dann lösche alle Differenzen
Sheets("RV_Belege").Range("A4:BB30000").ClearContents

Range("ResetAfterSave") = vbNullString 'dann lösche dort die 1
Range("speicherswitch") = "Kommentar" 'setze von Datei auf Kommentar
Tabelle2.Visible = False 'xlSheetVeryHidden 'blende die beiden Blätter aus
Tabelle3.Visible = False 'xlSheetVeryHidden
End If

Application.EnableEvents = True 'ab hier wieder alles sehen
End If
'''Range("speicherswitch") = "Kommentar"
End Sub


So if I remove the comments (like I did above), I cannot save anymore. Any workaround I can think of, would let me save - but then I wouldn't be able to get rid of this workaround afterwards, because it wouldn't let me save that.

Thanks so much guys!!

[SamT]

Quick and dirty workaround:

Insert new sheet. Name it "Trix"

At top of BeforeSave sub above

On Error Resume Next
If Sheets("Trix") is Nothing Then
GoTo NormalOperation
Else
Application.DisplayAlerts = False
Sheets("Trix").Delete
Application.DisplayAlerts =True
Cancel = True 'Might have to go after "Me.Save"
Me.Save
Exit Sub
End If
NormalOperation:

[GTO]

Greetings Kevin,

Just another thought, but if you are rarely making changes (i.e. you modified the code a bit and do not expect to make further changes), maybe try just putting a breakpoint at the top of BeforeSave. Then when you go to save, it will break at this line. Scroll down to the bottom of the sub, clicking in the End Sub line. Right-click and choose Set Next Statement. The yellow highlight (representing the code about to be executed will be moved there, which will have skipped past all the code you don't want executed this one time.

Then just press F5, and the Save will complete.

Hope that helps,

Mark

[KevinPhilips]

Awesome, thanks SamT and GTO!!

[snb]

You can always open a file macros disabled (shift open). So you can edit the code and save it. (unheimlich einfach).

[Teeroy]

snb, nice simple solution.


(unheimlich einfach)

my German is rusty is that frighteningly simple or simply frightening :think: (quite a difference).

[snb]

In this case unheimlich means: awfully ( but there might be more sophisticated slang expressions). So your first interpretation is the correct one: awfully simple.
In dutch you could say : 'wreed simpel' or 'kei simpel'.

[Jan Karel Pieterse]

I'd add a small routine in a normal module, like so:

Sub SaveAnyway()
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub