-
Feb 12th, 2018, 02:35 PM
#1
Thread Starter
Member
[RESOLVED] AutoSave
Hello
I am using Excel 2010 and want to know if I can use the command ActiveWorkbook.Save to
trigger when a value in a cell is changed? I wonder if there is a cell change function.
I know there is a way to set it thru Options but it isn't working. I have it set to one minute.
I read where it doesn't work well with a workbook that has macros in it.
Thanks for any help!
-
Feb 12th, 2018, 02:47 PM
#2
Thread Starter
Member
Re: AutoSave
My apologies I found this code that I'll work with ...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:C10")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."
End If
End Sub
-
Feb 12th, 2018, 03:12 PM
#3
Re: AutoSave
if this does what you require, pls mark thread as resolved
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 12th, 2018, 04:02 PM
#4
Thread Starter
Member
Re: AutoSave
Yes, I will but so far it's not working.
Do you recommend putting this code in a module or just in the body of code?
You can tell I'm a newb
-
Feb 13th, 2018, 04:11 AM
#5
Re: AutoSave
the code MUST go in the module for the specific worksheet
you can select the worksheet_change event from the 2 dropdown boxes at the top of the module, then just paste the above code minus the sub /end sub lines
ideally rather than activeworkbook.save, use thisworkbook.save, just in case, at any time, they are not the same thing
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 13th, 2018, 03:19 PM
#6
Thread Starter
Member
Re: AutoSave
Hello
This works pretty well except that just entering a cell with the cursor triggers the save before the actual value is entered.
I suppose there is a way to check the value entered for a non space or check that a character is entered.
-
Feb 13th, 2018, 03:34 PM
#7
Re: AutoSave
the worksheet change event fires after you have changed the value and leave the cell, not when entering the cell
Code:
msgbox target.address
will indicate which cell is the one that has been changed
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 13th, 2018, 03:45 PM
#8
Thread Starter
Member
Re: AutoSave
I see I can use the
Worksheet_Change(ByVal Target As Range) instead and it waits for a value.
-
Feb 13th, 2018, 03:49 PM
#9
Thread Starter
Member
Re: [RESOLVED] AutoSave
Thanks for your help Mr West!
-
Feb 14th, 2018, 03:18 AM
#10
Re: [RESOLVED] AutoSave
i thought that was what you were using, as in post #2
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 14th, 2018, 02:31 PM
#11
Thread Starter
Member
Re: [RESOLVED] AutoSave
Yes, you are right got my wires crossed
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|