Force the workbook to save after data entry

[Nik1halo]

Hi,

I have a table that is being used as a log to record safety tests on our products.

Each row is a new test (with a unique ID number in column A), which will be edited on multiple occasions upon completion of various tasks, including raising the request, producing the item and performing the test, including the pass/fail result.

Sometime, multiple tests will be entered/edited at one time.

Can anyone tell me whether it is possible to force excel to save the workbook after every record is updated, before moving to the next row/record?

I realise that we're getting into the realms of "Wouldn't this be better in an Access DB", but using macros in excel is a novel idea to my boss, so trying to convince him to use another program is out of the question!

 

[Rijnsent]

Hi Nik1halo,

that is kind of possible, but I guess you'd get some unhappy users. I you go to the VBA backend, you could add some code to the worksheet with your data on it:

Private Sub Worksheet_Change(ByVal Target As Range)

Do something here? Check if the row has changed, if so, save the file?

End Sub

You could e.g. save the file or a backup copy of the file after a row change, but that would be pretty annoying for a user, as that would happen every other click. You could also save the file on closing the file or changing the sheet, that is already a bit more user-friendly. But why do you want to save that file over and over? To get a kind of safety that people don't forget it?

Cheers,

Koen

 

[Nik1halo]

Hi Rijnsent, thanks for the reply.

The idea is that after every entry/edit of a record, the user will be prompted to choose from a list, what changes they have just made.

This will then be emailed out to a mail group, with the details of what has changed, so that the next person knows that there is work to pick up.

I've already sorted all the code for these parts, using Workbook_BeforeSave, it's just the code for checking if a row has changed and forcing a save if true that I'm struggling with.

 

[Rijnsent]

Hi Nik1halo,

you could give this a try, this code runs (the IF bit) if you select a different row then before:

Public OldRw As Long
Public NewRw As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

NewRw = Target.Row
If OldRw <> NewRw Then
    Debug.Print OldRw, NewRw
    OldRw = Target.Row
End If

End Sub

Cheers,

Koen

 

[Nik1halo]

Ah, that worked perfectly!

Thank you for your help with this.

My boss will probably decide that it's annoying having to go through the save process every time (which is what I originally said), but that's his problem, lol.