Database version control

[rireland]

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

 

[Airfix9]

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:~

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:~

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]

Thanks - will try it & let you know.
R

 

[rireland]

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]

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]

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]

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]

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]

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
	A	B	C	D
1	8	Fortitude	BonnieHall
2	9	SimplySmudge	HayleyMcCarthy
3	10	Norcara	LauraJohnson
4	11	RiverDancer	StaceyPlumbridge
5	12	RoyalStorm	BrittanyKirkman
6	13	SilverClaws	EmilyRuffo
7	14	GoldenGlory	MalikaManilow
8	15	Cherokee	DanielleBrocklehurst
9	16	SnowMask	SamanthaStone
10	17	L.C.Latte	JessicaPyke
11	18	HilltopElska	AdrianWalton
12	19	WilliamFrost	NicArcher
13	20	JustPippa	EmmaEbbelaar
14	21	ValleyParkSardis	LauraWalker
15	22	L.C.Cappuccino	SarahPyke
16	23	KerrileaMyMeekie	SamanthaMcCarthy
17	24	DarkIntentions	CallieStubbs
18	25	P.K.Gum	ErinBowman
19	26	Dr.Zeuss	BonnieHall
20	27	HawthornRose	BrookeSmithers
21	28	AceofHearts	JessBecker
22	29	LadyCandine	MorganDavies
23
24
25
26
Entries

 

[Airfix9]

Have PMd you my email address