PDA

View Full Version : Solved: Need Auto Save once value is entered in cell.



valendj
11-07-2011, 08:19 AM
Hello,
Does anybody know the VB code for setting up an auto save once a value is entered into a cell? I am currently using the auto save timer add in but I would like to have the file saved once a cell is activated in column M4:M1000 of the a worksheet. Thank you for your help!

mdmackillop
11-07-2011, 11:51 AM
Copy this into the Worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("M4:M1000"), Target) Is Nothing Then ActiveWorkbook.Save
End Sub

valendj
11-08-2011, 11:42 AM
This works if I type a value into the cell but not if I use a time stamp function when I double click on the cell. Does this make sense? So I have vb code which when cell M4:M1000 is double clicked the date and time are auto imputed into the cell. Excel does not realize that there is a value or data entered into the cell by the function manner. Any way to overcome this? Thanks again for your help you’re a huge life saver!

mdmackillop
11-08-2011, 12:36 PM
You can add formatting to Target as required
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Range("M4:M1000"), Target) Is Nothing Then
Target = Now()
ActiveWorkbook.Save
End If
End Sub

valendj
11-08-2011, 01:25 PM
I attached the workbook. The code does not work when I time stamp. The VB works when I delete the time stamp and manual type a value but not from a function. Any help would be greatly appreciated! Thank you!

mdmackillop
11-08-2011, 02:03 PM
Your code contains multiple events. Clear them and start again.
In the Sheet1 module
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Range("M4:M1000"), Target) Is Nothing Then
Target = Now()
Target.NumberFormat = "dd/mm/yy hh:mm"
ActiveWorkbook.Save
End If
End Sub


For Username, try
Function ReturnUserName() As String
ReturnUserName = Application.UserName
End Function

valendj
11-09-2011, 10:13 AM
I deleted all my code and started over and still yeiding the same results. If I double click on the cell it will not save but if I delete the time stamp function I used to input the date and time the sheet will save.

mdmackillop
11-09-2011, 10:55 AM
This works for me. Am I missing something?

valendj
11-09-2011, 11:13 AM
Thank You all! It works! Vbax is an awesome site!

mahi
02-09-2014, 02:27 AM
This works for me.