Database version control

rireland

Board Regular
Joined
Feb 12, 2004
Messages
64
We have a club membership database in Excel 2003. Unfortunately too many people can change it and we sometime use an old version for a mailout when there is a later version around. We have no easy, foolproof way of establishing which is the latest version.

Is there a way that a cell in the database can automatically have the date & time it was last changed (NOT last time it was read - Windows does that). Also a user name.
Failing that, is it possible to detect when anything has changed & when you try to close the file it requests a date & user which have to be entered before it closes ( this relies on the user putting in valid info but at least it's a start).
Ideally when you re-open the file it should say in A1, A2 - last changed .... by .....

Richard
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
I have some code which deals with this. One question to ask yourself is "what happens if it is saved twice between the times you use it?" If that is not a problem, use the first code. If it is, use the second (which produces a list of dates and times and users who saved it).

Put a hidden sheet called "Data" or some such into the file.

1st code (only saves the info from the latest save). Place in the "ThisWorkbook" module:~

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Sheets("Data").Range("A1").Value = Now
    Sheets("Data").Range("B2").Value = Environ("Username")
    
End Sub

2nd code (creates a list of saves). Again, place in the "ThisWorkbook" module:~
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim lrow As Long
    
    lrow = Sheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
    
    Sheets("Data").Range("A" & lrow).Value = Now
    Sheets("Data").Range("B" & lrow).Value = Environ("Username")
    
End Sub

Hope that helps.
 

rireland

Board Regular
Joined
Feb 12, 2004
Messages
64
Doesn't seem to work. I'm not a VB user but it first said "expect endsub" so I put End Sub at the end of the code. Then I got run time error 1004 - object or applicaion definition error. The line Sheet.... Now was highlighted in yellow.
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886

ADVERTISEMENT

Did you put in the hidden sheet called "Data"? If you are using a different sheet name, substitute that name for "Data" in my code.

Did you put the code into the "ThisWorkbook" module?

Which version of my code are you using?
 

rireland

Board Regular
Joined
Feb 12, 2004
Messages
64
yes, yes & 2nd version
using Excel 2003
this is the code I've used. Will it detect any change - like 1 digit in a 10,000 record database?

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

Dim lrow As Long

lrow = Sheets("Data").Range("A65536").End(xlUp).Offset(1, 0)

Sheets("Data").Range("A" & lrow).Value = Now [this line highlighted}
Sheets("Data").Range("B" & lrow).Value = Environ("Username")
End Sub
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886

ADVERTISEMENT

It will run the procedure every time the file is saved. No idea why it is failing. Is there any way you can send me the workbook for me to look at?
 

rireland

Board Regular
Joined
Feb 12, 2004
Messages
64
Don't know if this helps - when I try to close the file - I can't - there's a red box with a tick next to "close" - I can only get out by "exit"
 

rireland

Board Regular
Joined
Feb 12, 2004
Messages
64
Here is the code that is in workbook plus the html version of the database. This is a dummy database as the real one is too big for the html convertor - but I got the same error. Not sure if this convertor includes the code or not - if not can I have your email so I can send you the file including the code. Thanks Richard

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

Dim lrow As Long

lrow = Sheets("Data").Range("A65536").End(xlUp).Offset(1, 0)

Sheets("Data").Range("A" & lrow).Value = Now
Sheets("Data").Range("B" & lrow).Value = Environ("Username")

End Sub
test for version control.xls
ABCD
18FortitudeBonnieHall
29SimplySmudgeHayleyMcCarthy
310NorcaraLauraJohnson
411RiverDancerStaceyPlumbridge
512RoyalStormBrittanyKirkman
613SilverClawsEmilyRuffo
714GoldenGloryMalikaManilow
815CherokeeDanielleBrocklehurst
916SnowMaskSamanthaStone
1017L.C.LatteJessicaPyke
1118HilltopElskaAdrianWalton
1219WilliamFrostNicArcher
1320JustPippaEmmaEbbelaar
1421ValleyParkSardisLauraWalker
1522L.C.CappuccinoSarahPyke
1623KerrileaMyMeekieSamanthaMcCarthy
1724DarkIntentionsCallieStubbs
1825P.K.GumErinBowman
1926Dr.ZeussBonnieHall
2027HawthornRoseBrookeSmithers
2128AceofHeartsJessBecker
2229LadyCandineMorganDavies
23
24
25
26
Entries
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top