none
save worksheet after a value is entered RRS feed

  • Question

  • Hi!

    I already use a <g class="gr_ gr_11 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="11" id="11">vba</g> code that locks cell after a value is entered!  What I also need is the worksheet to be saved every time a new value is entered!  

    Thanks,

    Vassilis


    • Edited by VassilisTr Thursday, May 7, 2015 6:47 AM
    Thursday, May 7, 2015 6:46 AM

Answers

  • Hi

    Before your" End Sub", just add this   "ActiveWorkbook.Save"


    Cimjet


    • Edited by Cimjet Thursday, May 7, 2015 10:00 PM
    • Proposed as answer by Cimjet Wednesday, May 13, 2015 12:07 PM
    • Marked as answer by VassilisTr Friday, May 15, 2015 9:02 AM
    Thursday, May 7, 2015 8:27 PM
  • Hi

    Well, this line " ActiveWorkbook.Save" can't cause your script to stop running. I tried your script on a blank worksheet and it works fine if the cells are unlock. Can you post a link to a sample file that is giving you the problem.

    You didn't say if you had any error message.


    Cimjet

    • Marked as answer by VassilisTr Friday, May 15, 2015 8:57 AM
    Saturday, May 9, 2015 12:02 AM
  • You're welcome

    Would you mark the post "Answered"

    Thanks


    Cimjet

    • Marked as answer by VassilisTr Friday, May 15, 2015 8:57 AM
    Tuesday, May 12, 2015 10:44 AM

All replies

  • Hello,

    I'm not sure what you mean, because your code does not look of anything that comes out of excel, but maybey this helps:

    Your worksheet has an event you can trigger:  Worksheet_Change.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Call ThisWorkbook.Save
    End Sub

    Best regards,

    Wouter

    Thursday, May 7, 2015 8:53 AM
  • this is the code i use:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        ActiveSheet.Unprotect Password:="Athens"
        For Each cel In Target
            If cel.Value <> "" Then
                cel.Locked = True
            End If
        Next cel
        ActiveSheet.Protect Password:="Athens"
    End Sub

    And I want it to autosave the worksheet right after a value is entered!

    thanks,

    Thursday, May 7, 2015 7:07 PM
  • Hi

    Before your" End Sub", just add this   "ActiveWorkbook.Save"


    Cimjet


    • Edited by Cimjet Thursday, May 7, 2015 10:00 PM
    • Proposed as answer by Cimjet Wednesday, May 13, 2015 12:07 PM
    • Marked as answer by VassilisTr Friday, May 15, 2015 9:02 AM
    Thursday, May 7, 2015 8:27 PM
  • did it like that but doen't work!

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        ActiveSheet.Unprotect Password:="ad174326"
        For Each cel In Target
            If cel.Value <> "" Then
                cel.Locked = True
            End If
        Next cel
        ActiveSheet.Protect Password:="ad174326"
        ActiveWorkbook.Save
    End Sub

    Thursday, May 7, 2015 10:18 PM
  • Hi

    Please explain what doesn't work!

    It should save the workbook every time the script run


    Cimjet

    Thursday, May 7, 2015 10:36 PM
  • hi, 

    it doen't automaticaly save it!  and also my previus vba which was working, locking the cell after a vavue was enered, now it doen't!!!

    thanks,

    Friday, May 8, 2015 5:44 AM
  • Hi

    Well, this line " ActiveWorkbook.Save" can't cause your script to stop running. I tried your script on a blank worksheet and it works fine if the cells are unlock. Can you post a link to a sample file that is giving you the problem.

    You didn't say if you had any error message.


    Cimjet

    • Marked as answer by VassilisTr Friday, May 15, 2015 8:57 AM
    Saturday, May 9, 2015 12:02 AM
  • Yes works thanks!!!

    forgot to enable active content, that's why!!!

    Tuesday, May 12, 2015 6:57 AM
  • You're welcome

    Would you mark the post "Answered"

    Thanks


    Cimjet

    • Marked as answer by VassilisTr Friday, May 15, 2015 8:57 AM
    Tuesday, May 12, 2015 10:44 AM
  • Did that thanks!

    Another question now:  what is the code if I want to save it automatically after 1 minute and not right after a value is entered???  Need that because saving all the time after each value is entered is too much time consuming as you have to wait for it to save the doc!


    • Edited by VassilisTr Friday, May 15, 2015 9:02 AM
    Friday, May 15, 2015 9:01 AM
  • Hi

    Sorry to take so long to reply but here in Canada, it's a legal Holiday today and was gone for the long weekend.

    It can be done but every entry you make, it keeps it in memory and if you make 5 or 6 entries, it will save 5 or 6 time. Not practical at all.

    I can't think of any way to do it properly except saving manually.


    Cimjet


    • Edited by Cimjet Monday, May 18, 2015 10:47 PM
    Monday, May 18, 2015 10:46 PM