Eyes are crossed ... Need help creating a log file from an excel workbook

[Saffire]

ray:

I could really use some help here. I have a project that started off simple enough, and has now become a nightmare.

I need to create a log file from an excel work book. I only need one cell (ending balance) to write to the log file each time the shift supervisor saves the workbook.

In another cell (beginning balance), I need the work book to read the last entry from the log file. I have been going crazy trying to do this. I use to consider myself proficient in excel until I met VBA. Now, I realize that I know NOTHING!

Thanks for any and help.

Saffire

 

[Michael M]

Hi Saffire and welcome to the Board
This may help.
I have created a sheet named "log" and run the following code at workbook open to record an "opening value" and also the date and user name. It then saves the workbook
If you want it to add a value when the supervisor closes the workbook you'll need to to do the same thing with a workbook close event
You'll need to change the reference in red to suit

Sub workbook_open()
Dim lra As Long
lra = Sheets("log").Range("A" & Rows.Count).End(xlUp).Row
lra=lra+1
Sheets("log").Range("A" & lra) = VBA.Now
Sheets("log").Range("B" & lra) = VBA.Environ("username")
Sheets("log").Range("C" & lra) = Sheets("Sheet1").Range("A1").Value
ActiveWorkbook.Save
End Sub

 

[Saffire]

Michael M. Thank you thank you thank you ... that has worked perfectly ... I was even able to figure out how to switch it to a close_workbook statement and now everything works as it is suppose to ... I wish that I had swallowed my pride sooner and asked for help ... that will teach me in the future.

Saffire

 

[Michael M]

Glad it worked...and thanks for the feedback...
Rather than "swallowed the pride", consider it as "I tried my best, and learned something along the way"......just needed a little help, as we all do sometimes !!