To use this method, you need to do a little set up of your workbook.
Specifically you need to add a Name to the workbook and give it an initial
value for the levels it is at. Use Insert | Name | Define
enter a name; I used wbVersion
then in the 'Refers to' section, simply enter the version like
1.2.3.4
(or more likely 1.1.1.1 initially?)
[OK] and you're ready to go. The code will use that internally stored value
to keep up with things for you.
There's a working example workbook with all of this in it that you can
download from:
http://www.jlathamsite.com/uploads/revision.1.1.1.1.xls
Simply click the link and Save to your hard drive.
You also need a UserForm - or you could use a series of InputBoxes, but I
went with a userform. It has a label telling the user to check appropriate
checkboxes or clear all for no change. It has one command button to execute
the code that does the work. You'll see the names for the form and controls
on it in the code below.
You could have a macro to be called manually to perform the operation 'on
demand' that would look like this:
This code would go into a regular code module.
'this public variable needed to communicate
'with the userform code to tell it that the
'user is manually updating the version #
'so don't close the workbook at this time
Public DontCloseWorkbook As Boolean
Sub UpdateVersion()
'user is updating the version #
'so don't automatically close the workbook
DontCloseWorkbook = True
'open the form that does the work
VersionUpdater.Show
'reset the flag
DontCloseWorkbook = False
End Sub
Here is the code attached to the userform's one command button, followed by
the form's Initialize() event process.
Private Sub cmdCommitChanges_Click()
Dim versionID As String
Dim splitValues As Variant
Dim fileName As String
Dim fullPath As String
'get current version ID stored in Name wbVersion
versionID = Mid(ThisWorkbook.Names("wbVersion").RefersTo, _
3, Len(ThisWorkbook.Names("wbVersion").RefersTo) - 3)
'break versionID down into individual pieces
splitValues = Split(versionID, ".")
'splitValues(0) = Functional Level
'splitValues(1) = Formula Level
'splitValues(2) = Format Level
'splitValues(3) = Data Entry Level
If Me!chkFunctionalChange = True Then
splitValues(0) = splitValues(0) + 1
End If
If Me!chkFormulaChange = True Then
splitValues(1) = splitValues(1) + 1
End If
If Me!chkFormatChange = True Then
splitValues(2) = splitValues(2) + 1
End If
If Me!chkDataEntryChange = True Then
splitValues(3) = splitValues(3) + 1
End If
fileName = ThisWorkbook.Name
'the file name may not have a . in the name
'until the character before the first
'(Functional Level) indicator.
'remove all after first .
'
'filename didn't have a . in it!
'may be new book, not yet saved
'and has name like Book1
'in which case we just kind of ignore it for now
If InStr(fileName, ".") Then
fileName = Left(fileName, InStr(fileName, ".") - 1)
End If
'now rebuild the filename using revised values
'in array splitValues()
fileName = fileName & "." & _
Trim(Str(Val(splitValues(0)))) & "." & _
Trim(Str(Val(splitValues(1)))) & "." & _
Trim(Str(Val(splitValues(2)))) & "." & _
Trim(Str(Val(splitValues(3)))) & ".xls"
'find the path to save it to
fullPath = ThisWorkbook.FullName
fullPath = Left(fullPath, InStrRev(fullPath, "\"))
fileName = fullPath & fileName
'update the internal copy of the version ID
versionID = "=" & Chr$(34) & _
Trim(Str(Val(splitValues(0)))) & "." & _
Trim(Str(Val(splitValues(1)))) & "." & _
Trim(Str(Val(splitValues(2)))) & "." & _
Trim(Str(Val(splitValues(3)))) & Chr$(34)
ActiveWorkbook.Names.Add Name:="wbVersion", _
RefersToR1C1:=versionID
Application.DisplayAlerts = False
ThisWorkbook.SaveAs fileName:=fileName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
If DontCloseWorkbook = False Then
'will only be False if userform opened by
' call from _BeforeClose() event
ThisWorkbook.Close
End If
Unload Me ' release object resources
End Sub
Private Sub UserForm_Initialize()
Me!chkFunctionalChange = False
Me!chkFormulaChange = False
Me!chkFormatChange = False
Me!chkDataEntryChange = False
End Sub
Finally, this code goes into the Workbook_BeforeClose() event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Static CloseInProgress As Boolean
If DontCloseWorkbook Then
Cancel = True
Exit Sub
End If
DontCloseWorkbook = False
If Not CloseInProgress Then
'prevent error caused by
'trying to open form while already open
CloseInProgress = True
VersionUpdater.Show
End If
End Sub
bernd said:
Hello,
A couple of months ago I started to create a program in excel, what a
magnificant program this is!! I'm learningmore and more about VBA, but
I absolutely have no clue how to realize the idea I've got now.
The idea is about the versioning of the file.
Each time the file is edited the versioning should number op.
There are 4 levels of changes that can be made:
1. new functionality
2. Formula editing
3. Formatting the file
4. Data entry
The file is named like: file.[level1].[level2]..[level3].[level4].
Example is like -> file.1.1.1.1 This means each level on first stage.
By calling a macro I want the user to be asked what kind of change has
been made or to choose the option "no chages made". This is followed
saving the file with numbering up the version number to the right
level. (like Save As)
The realizing of the numbering has to be made in the excel file (so no
external file like .txt). (Optionally with version history where also
an comment can be added)
If no idea of how to realize this, or where to start. If some of you
experts could help me out here, would be GREAT!
Thanks in advance,
Bernd
