Looking for help with an Excel Macro

Taramoor

Real quick question, hopefully:

I have a large shared workbook at my office, and part of my job is monitoring that everyone updates it regularly.

I use the following right now to monitor changes:

Option Explicit

Dim Previous As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Previous = Target.Formula
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)

.Offset(1, 0).Value = Environ("UserName")
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = "'" & Target.Formula
.Offset(1, 4) = Previous
Previous = ""
.Offset(1, 5) = Now
End With
Application.EnableEvents = True
End Sub

The problem we run into right now is that the Log sheet updates in real time, so if more than one person has the workbook open while making changes, they get that irritating "Someone else has changed a cell you changed!" pop-up when they eventually go to save.

Is there a way to make the file update that log page before completing the save action, or move all of the updates down to compensate, or any sort of way I can compensate for the fact that of the dozen or so teams I'm managing at any given time at least a few of them are idiots?

ceres

@ElJeffe

ElJeffe

Sorry, mucking about with that sort of change tracking and file saves is outside my wheel house. I'm curious to see what other people have to say, though.

Inquisitor77

This sounds a lot like a problem with version control. I have no idea how you would manage it with macros, given there are basically entire product lines that revolve around this kind of thing. Microsoft has OnePlace (formerly Sharepoint) that lets you check documents out and check them back in to prevent other users from making changes concurrently in the first place.

I mean, there's really no way to avoid the problem you're describing. You can mitigate it by forcing users to make the log update immediately upon opening, but you would still have all the normal concurrency issues.

Dis'

Taramoor wrote: »

Real quick question, hopefully:

I have a large shared workbook at my office, and part of my job is monitoring that everyone updates it regularly.

I use the following right now to monitor changes:

Option Explicit

Dim Previous As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Previous = Target.Formula
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
With Sheets("Log").Cells(Rows.Count, 1).End(xlUp)

.Offset(1, 0).Value = Environ("UserName")
.Offset(1, 1) = Sh.Name
.Offset(1, 2) = Target.Address
.Offset(1, 3) = "'" & Target.Formula
.Offset(1, 4) = Previous
Previous = ""
.Offset(1, 5) = Now
End With
Application.EnableEvents = True
End Sub

The problem we run into right now is that the Log sheet updates in real time, so if more than one person has the workbook open while making changes, they get that irritating "Someone else has changed a cell you changed!" pop-up when they eventually go to save.

Is there a way to make the file update that log page before completing the save action, or move all of the updates down to compensate, or any sort of way I can compensate for the fact that of the dozen or so teams I'm managing at any given time at least a few of them are idiots?

Like Inquisitor77 said this is usually something addressed via version control software.

However you could mock up version control by adding functions to the BeforeSave event - essentially telling it to sort out the log page before saving by comparing its log version to what is in the current master and shifting its updates to compensate. You'll need to be careful and think through exactly what you want to happen when it does this.

Thundyrkatz

We ran into a similar issue in our office and in the end we converted the whole system to use the List function on Sharepoint. Its fantastic, you have user control you get update notifications if you want, remote access and backup are a breeze and you can really control how the data is entered to limit users from breaking formulas or mucking up reports.

Nija

We use a non-relational database for these types of things, because you can't have multiple people editing a single Excel file.

Tofystedeth

Yeah, I know it's not answering the question you actually asked, but this is a pretty suboptimal use case for Excel.

Taramoor

Tofystedeth wrote: »

Yeah, I know it's not answering the question you actually asked, but this is a pretty suboptimal use case for Excel.

True, but I have to use the sheet mandated by our client and management, so I'm just trying to make it work.

Under normal circumstances I would just use the built-in History and Change tracking that comes with a shared Excel doc, but there are sometimes a thousand edits in a day, and sometimes it needs to be unshared briefly to update formulas and that wipes the history.

Darkewolfe

Could you use a web form which just dumps input from those users into excel, or do they need to edit as well?

Tofystedeth

Darkewolfe wrote: »

Could you use a web form which just dumps input from those users into excel, or do they need to edit as well?

By the end of this thread we'll have pioneered the WSEA stack.

Tofystedeth

I'm not sure what the format of your log is, but if there's a fixed set of people updating this document, each person could be assigned a column(s). Since you're collecting their usernames (I think, I'm not super deep into VB and Excel macro syntax) you could then split the log output out to the different targets, so they're never overwriting someone else's log entry. If you need a more condensed log, have another sheet where those columns are all combined in datetime order at end of day or something.

Hmm maybe if you can get management to budge a little, do the logging in Access? I'm pretty I've seen before where excel can insert into an access DB via an OLEDB connection

Inquisitor77

Darkewolfe wrote: »

Could you use a web form which just dumps input from those users into excel, or do they need to edit as well?

This is actually a really good idea, as long as users are only making additions to the document and aren't actually editing existing content concurrently. This way the submissions will be handled sequentially in the order they are received.

There might be ways to force the document to be locked in Shared mode as well. I'm only familiar with using the Check In/Out functionality in Sharepoint/OnePlace, but it looks like Office 365 supports it natively in Excel as well...

But if people really are making literally thousands of changes a day, that might be a huge hassle in terms of folks who accidentally check it out for long periods of time.

Taramoor

They are make hundreds of edits a day, concurrently, but the only time they'll be editing the same cell is when my little lump of code tracks their changes in the log sheet.

If I could somehow force them to save every so often it wouldn't be nearly as bad, but some people will keep the shared doc open for entire shifts before saving and updating everything.

Inquisitor77

It really sounds like some sort of form submission is probably a huge improvement over the existing process. At that point the question becomes why are you editing the same cells over and over again, and if there is some way you can get the same information in a different way. For example, the constantly-edited cells are just a report or calculation of some kind that you really only need once each day, in which case you can just set that up as a query and run it whenever you need the actual data. Or if it is some kind of change log, then this would give you the freedom to regularly dump those records into an actual list somewhere for long-term tracking instead of overwriting the same records over and over again.

Tofystedeth

I think the overwriting just happens when it tries to log 2 people because the macro starts at the next empty cell which is the same for both.

Hooth

You're only getting a couple hundred updates a day? Stop writing them to the permanent log sheet; instead write them to a daily temp sheet and pick a random row (say between 1 and 100000) rather than the next empty one. End of day, sort on the time stamp and copy to the log sheet. You could still lose a change, but it is very very unlikely. Oh, and be sure to seed RAND properly or this wont work.

Dis'

Hooth wrote: »

You're only getting a couple hundred updates a day? Stop writing them to the permanent log sheet; instead write them to a daily temp sheet and pick a random row (say between 1 and 100000) rather than the next empty one. End of day, sort on the time stamp and copy to the log sheet. You could still lose a change, but it is very very unlikely. Oh, and be sure to seed RAND properly or this wont work.

You don't even need to distribute randomly in the temp sheet if you're recording usernames, just have a predefined pair of columns in the temp sheet for each username to put their {time:change} and at the end of the day run a process to amalgamate this into a single log.