Results 1 to 11 of 11

Thread: [RESOLVED] AutoSave

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Resolved [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!

  2. #2

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    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

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    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.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Re: AutoSave

    I see I can use the
    Worksheet_Change(ByVal Target As Range) instead and it waits for a value.

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Re: [RESOLVED] AutoSave

    Thanks for your help Mr West!

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] AutoSave

    I see I can use the
    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

  11. #11

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    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
  •  



Click Here to Expand Forum to Full Width