+ Reply to Thread
Results 1 to 4 of 4

Run Macro(save workbook) after cell updated

  1. #1
    Registered User
    Join Date
    02-04-2006
    Location
    Hinckley UK
    Posts
    5

    Run Macro(save workbook) after cell updated

    Hello I need a way of saving a workbook after I input something(a user name) into a cell in column A. I have recorded a macro to save the workbook I just need it to run when I update one of the cells. There is a total of 22 worksheets I have to apply this to so f there is a quick way of apply it to all the sheets that would be even better.

    Thanx

    Titch

  2. #2
    Otto Moehrbach
    Guest

    Re: Run Macro(save workbook) after cell updated

    The following macro will do what you want. Note that this macro will save
    the workbook (file) whenever the contents of ANY cell in Column A of ANY
    sheet in the workbook changes. If you want this to apply to only some of
    the sheets, you will have to add an IF statement or modify the one that is
    there to exclude certain sheets.
    Note that this macro is a workbook event macro. That means it has to be
    placed in the workbook module. To access this module, right-click on the
    Excel icon that is to the left of the word "File" in the menu line across
    the top of the screen display, select View Code, and paste this macro into
    that module. Please post back of you need more. HTH Otto
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 1 Then
    ThisWorkbook.Save
    ThisWorkbook.Saved = True
    End If
    End Sub

    "titch" <titch.22qi7c_1139106003.7676@excelforum-nospam.com> wrote in
    message news:titch.22qi7c_1139106003.7676@excelforum-nospam.com...
    >
    > Hello I need a way of saving a workbook after I input something(a user
    > name) into a cell in column A. I have recorded a macro to save the
    > workbook I just need it to run when I update one of the cells. There is
    > a total of 22 worksheets I have to apply this to so f there is a quick
    > way of apply it to all the sheets that would be even better.
    >
    > Thanx
    >
    > Titch
    >
    >
    > --
    > titch
    > ------------------------------------------------------------------------
    > titch's Profile:
    > http://www.excelforum.com/member.php...o&userid=31190
    > View this thread: http://www.excelforum.com/showthread...hreadid=508603
    >




  3. #3
    Registered User
    Join Date
    02-04-2006
    Location
    Hinckley UK
    Posts
    5

    Thanks

    That worked a treat.
    is there anychance you can explain whats going on in the code(line 4-7 are easy i know). I don't fully understand whats going on in the first 3 lines. Specificaly what would I change if it was column B, I think I have a fair idea but it is always good to know exactly what is happening.

    Cheers

    Titch

  4. #4
    Otto Moehrbach
    Guest

    Re: Run Macro(save workbook) after cell updated

    If Target.Count > 1 Then Exit Sub
    This means that if you change more than one cell at the same time, do
    nothing.

    If Target.Column = 1 Then
    If the change is in Column A, do what follows. B is 2, C is 3, etc.

    ThisWorkbook.Save
    Save the workbook

    ThisWorkbook.Saved = True
    When you save a workbook manually, the file is saved and it is flagged as
    being saved. Not so when you save by VBA. It is just saved. If you were
    to close Excel after that, you would get a query box asking if you want to
    save the file.
    This statement says to flag the file as Saved.
    HTH Otto




    "titch" <titch.22rem0_1139148003.3955@excelforum-nospam.com> wrote in
    message news:titch.22rem0_1139148003.3955@excelforum-nospam.com...
    >
    > That worked a treat.
    > is there anychance you can explain whats going on in the code(line 4-7
    > are easy i know). I don't fully understand whats going on in the first
    > 3 lines. Specificaly what would I change if it was column B, I think I
    > have a fair idea but it is always good to know exactly what is
    > happening.
    >
    > Cheers
    >
    > Titch
    >
    >
    > --
    > titch
    > ------------------------------------------------------------------------
    > titch's Profile:
    > http://www.excelforum.com/member.php...o&userid=31190
    > View this thread: http://www.excelforum.com/showthread...hreadid=508603
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1