Exit sub causing crash

[Newkiebrown]

I am not new to VBA but have never used it in anger and class myself as a beginner. However I wanted to make a piece of code that would pop up a form on save which lets a user select from 4 options. Some options will require further user in put into a messagebox and this will be used in a log sheet (I haven't done this part yet although this will be the easier part of the coding).
ChangeLog is a user form that has 4 radio buttons that allow a user to select which type of change they've made.

This my code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)​

Dim ModelPath As String, FullModelName As String, ModelPrefixName As String, ModelDocType As String, ModelVersion As String, ModelEndName As String, ModelSuffix As String​

Dim BigVersion As Integer, IncVersion As String, s As Integer, v As Integer, l As Integer, d As Integer​

Dim BigChange As Boolean, IncChange As Boolean, DiffZero As Boolean, SaveOnly as Boolean​

ChangeLog.Show​

ModelPath = ThisWorkbook.Path & "\"​

'Numbers needed to carve up the model name into prefix, version number and any suffix that may exist​

v = InStrRev(ActiveWorkbook.Name, " V") Or InStrRev(ActiveWorkbook.Name, " v") + 1​

s = InStrRev(ActiveWorkbook.Name, " ")​

d = InStrRev(ActiveWorkbook.Name, ".")​

'Splits the workbook into the document name and the application suffix​

FullModelName = Left(ActiveWorkbook.Name, d - 1)​

ModelDocType = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - d)​

' Capture if there is a suffix after the Version​

If s > v Then​

ModelPrefixName = Left(FullModelName, v - 2)​

ModelEndName = Right(FullModelName, d - s)​

ModelSuffix = Mid(FullModelName, v + 1, s - v - 1)​

BigVersion = Split(ModelSuffix, ".")(0)​

IncVersion = Split(ModelSuffix, ".")(1)​

' For the normal naming convention​

Else​

ModelPrefixName = Left(FullModelName, v - 2)​

ModelEndName = ""​

ModelSuffix = Mid(FullModelName, v + 1, d - s)​

BigVersion = Split(ModelSuffix, ".")(0)​

IncVersion = Split(ModelSuffix, ".")(1)​

End If​

Application.EnableEvents = False​

If ChangeLog.BigChange = True Then​

BigVersion = BigVersion + 1​

ElseIf ChangeLog.IncChange = True Or ChangeLog.DiffZero = True Then​

IncVersion = IncVersion + 1​

ElseIf ChangeLog.SaveOnly = True Then​

ActiveWorkbook.Save​

Application.EnableEvents = True​

Exit Sub​

End If​

IncVersion = format(IncVersion, "000")​

Unload ChangeLog​

ActiveWorkbook.SaveAs Filename:=ModelPath & ModelPrefixName & " v" & BigVersion & "." & IncVersion & ModelEndName & "." & ModelDocType​

Application.EnableEvents = True​

End Sub
​

The last end sub causes the code to crash excel but I have a feeling it is something else that is causing it. basically the code takes the current model and can either increment the "v1.001" by either a big change (adding 1 to the foremost figure) or a incremental change (adding 1 to the latter figure). It can also increment by 1 if the check pages in the document are reset (the diffzero) and then lastly can just do a normal save over the original document.

The model name can look like:

Model v1.001 sent to xxx.xlsm

My code wants to break it down (using the above as an exmaple) into:

1. Model name - Model
2. Version (big and small) - v1.001 where 1 is the big version and 001 is the small
3. a suffix applied after the version - sent to xxx
4. The document type - .xlsm (just in case someone decides to change the excel version for sharing

I created a userform (ChangeLog) which has the radio buttons relating to BigChange, IncChange, DiffZero, SaveOnly. depending on which is picked determines which part of the version is changed or none. The changelog form also has a cancel and ok button. The OK button simply does:

Changelog.Hide

The cancel button

unload me
end


I thought I would try and explain what I am trying to do in case there is a much easier way to do it. My code should be readable by others even if they don't know how to change it.

It was looping at the end save points so I have added Application.EnableEvents = False, which I read elsewhere on here. This does stop the coding from repeating itself but hasn't solved the crashing.

 

[CharlesChuckieCharles]

try adding

Cancel =true
Exit sub

at the bottom before the End Sub

Its weird because you're saving the activeworkbook as something else in the save the activeworkbook subroutine

so cancelling out and exiting maybe the best way to go

 

[Newkiebrown]

Putting the cancel in has solved the problem, which is great thanks. Any idea why it would cause it to crash when ending the sub? maybe because it had never had anything assigned to it and remained empty?

 

[CharlesChuckieCharles]

I think because you were invoking the save of the Activeworkbook then you're saving the activeworkbook with another name, in the middle of saving the activeworkbook

Thus the Cancel =True Cancels that implicit save

and exit sub ensures that the processing is not unexpectedly falling out of the module by directing the flow to exit subroutine