VBA to save shared workbook any time a cell is modified ?

[doctorevii2001]

I am completely new to VBA, but from what I know it seems this should be possible. Could anyone please walk me through how (and where) to enter a VBA that will automatically save the shared workbook any time any user changes any cell.

The practical application is that I have a PO log that multiple employees have open at the same time and when one needs a PO they enter their info, "claiming" that number. A problem arises when one has "claimed" one but not saved and then another person tries claiming the same one.

Alternately a VBA that saves every second would work the same.

Again, I'm a super green newbie at this, so please talk to me like a little child

Thanks for your help!

 

[igold]

Hi doctorevii2001,

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ThisWorkbook.Save
End Sub

Insert VBA code to Excel Workbook
1. Open your workbook in Excel.
2. Press Alt+F11 to open the Visual Basic Editor (VBE).
3. Double-click on “ThisWorkbook” (at the top left corner of the editor window).
4. Copy the VBA code (from this page) and paste it to the right pane of the VBA editor ["ThisWorkbook (Code)]" window).
5. Save your workbook as "Excel macro-enabled workbook". Press Crl+S, then click the "No" button in the "The following features cannot be saved in macro-free workbook" warning dialog.
The "Save as" dialog will open. Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button.


The workbook should auto save whenever there is a change on any worksheet.


HTH

igold

 

[doctorevii2001]

Hi doctorevii2001,

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ThisWorkbook.Save
End Sub

The workbook should auto save whenever there is a change on any worksheet.


HTH

igold

Awesome, thank you so much! Taking that one step further I don't suppose it would work to do the same behavior any time a cell is selected (as opposed to just modified)? If not, no worries, this gets me 99% of the way there. I really appreciate your response and help!

Cheers!

 

[igold]

I am not familiar with getting a macro to trigger by the mere selection of a cell, but clearly that doesn't mean it cannot be done. Perhaps one of the more knowledgeable coders will chime in with a solution.

Thanks for the feedback. I am glad that I was at least partially able to help.

Regards,

igold

 

[rollis13]

A "SelectionChange" instead of "SheetChange" would do but saving the workbook at every cell-click would be a heavy job for Excel.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[doctorevii2001]

A "SelectionChange" instead of "SheetChange" would do but saving the workbook at every cell-click would be a heavy job for Excel.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I was able to do it with the sheet VBA below. Causes a slight delay with each cell selection, but minimal. For a page that doesn't have a ton of data (right now it's 49 kb for example) I would think it shouldn't have any problem, yeah?

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A1:M606")) Is Nothing Then
            ThisWorkbook.Save
        End If
    End If
End Sub

 

[doctorevii2001]

A "SelectionChange" instead of "SheetChange" would do but saving the workbook at every cell-click would be a heavy job for Excel.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Taking your advice that it might be a little resource heavy I came up with a different solution:

Ultimately I wanted to make sure that no one grabbed the same PO# on the shared workbook, so I created a button at the top that is tied to the following Macro:

Sub New_PO()
            ThisWorkbook.Save


    Cells(6, 2).Select ' starts at cell B6
    Do Until ActiveCell = ""
       ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell = Date
    MsgBox (ActiveCell.Offset(0, -1))
    ActiveCell.Offset(0, 1).Select
            ThisWorkbook.Save
End Sub

Thank you all for your input and help, I genuinely appreciate it!!!