Self-saving workbook?

[frubeng]

Hello,

I have uploaded the workbook for your reference. Basically, data in column K updates frequently throughout the day. I want the workbook to save itself after each change, or just regularly.

I have another workbook where i wanted something to happen regularly and i just called it in the "Sheet1 (Sheet1) " part of the VBA editor. It doesnt do it here. I am not really sure why.

Thanks a lot for any assistacne here!

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "K:K0" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Parent.Save
End If

ws_exit:
Application.EnableEvents = True
End Sub



This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[frubeng]

Thanks. So i put in that code in the Sheet1( Sheet1) section, but it doesn't seem to be working.
I attached the workbook, and also added a funciton to change the color of cells(1,1) to see if it is called, but it isnt :(

[Bob Phillips]

One parent too few



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "K:K" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Parent.Parent.Save
Cells(1, 1).Interior.ColorIndex = 3
End If

ws_exit:
Application.EnableEvents = True
End Sub

[frubeng]

OK, so this works, but not really for what i need.
The values in Column K change, not by the user changing them (which right now seems to be the only trigger) :(

I was thinking of doing a funciton such as


Function ToSave()
ActiveWorkbook.Save
End Function


and then have a cell such as "=Tosave()" which would refresh everytime hence recaclucalte and thus save, but it doesn't seem to work...

Thanks for the help, any ideas on this?

[Bob Phillips]

Use the worksheet calculate event.

[frubeng]

Use the worksheet calculate event.

Not sure what you mean, I've replaced the Worksheet_Change by Worksheet_Calculate, but that just gives me an error message (compile error).

[frubeng]

Public Event Calculate()

Private Sub Worksheet_Calculate() '(ByVal Target As Range)

'Const WS_RANGE As String = "K:K" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
'If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Parent.Parent.Save
Cells(1, 1).Interior.ColorIndex = 4
'End If
ws_exit:

End Sub


I changed this which compiles but doesn't work :(

[Bob Phillips]

Just use



Private Sub Worksheet_Calculate()
ThisWorkbook.Save
End Sub


but it will be busy!

[frubeng]

Just use



Private Sub Worksheet_Calculate()
ThisWorkbook.Save
End Sub


but it will be busy!

It doesn't do anything :( I've attached it. I don't know what is going wrong.