Run Macro(save workbook) after cell updated

[titch]

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

 

[Otto Moehrbach]

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]

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

 

[Otto Moehrbach]

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