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


-- 
titch
------------------------------------------------------------------------
titch's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31190
View this thread: http://www.excelforum.com/showthread.php?threadid=508603

0
2/5/2006 2:19:59 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
446 Views

Similar Articles

[PageSpeed] 52

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?action=getinfo&userid=31190
> View this thread: http://www.excelforum.com/showthread.php?threadid=508603
> 


0
ottokmnop (389)
2/5/2006 12:49:27 PM
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?action=getinfo&userid=31190
View this thread: http://www.excelforum.com/showthread.php?threadid=508603

0
2/5/2006 1:55:42 PM
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?action=getinfo&userid=31190
> View this thread: http://www.excelforum.com/showthread.php?threadid=508603
> 


0
ottokmnop (389)
2/5/2006 7:55:41 PM
Reply:

Similar Artilces:

Cond Format & helper-cell based "duplicate rec" tricked by content
Using 2003 Goal was to use Conditional Format and/or a helper-column cell to isolate duplicated records in a range. The formulas used were: Conditional Format =IF(COUNTIF(Range1, B5)>1,TRUE,FALSE) (Cell turns Yellow) Contigious cell =IF(COUNTIF(Range1,B2)>1,"Duplicate","") All of below cells do NOT have a duplicate thru 7 characters! But XL senses duplicates via both above formulas! M*D9000 M*D5000 M*D0004 M*D0035 M*D0002 Is there a "Bug" in XL that may see the second letter "*" as a wildcard OR stops the compare at "M*" ?...

Getting exact cell / Range from the pie chart
By selecting data point on the pie chart, I want to get corresponding cell/range ( e.g E6 or E6:E8). I can get the values ( ActiveChart.SeriesCollection(1).Values), but can't seem to find how to get exact cell. Will appreciate your reply. Thanks Suyog Suyog - Excel doesn't make it easy. You can get the series formula, and parse it to extract the range of interest. John Walkenbach shows how to use a class module to do just this on his web site, http://j-walk.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://Peltie...

Combining cell values
I have a list which has companyname and contracts numbers in column A & B Example Company Name Contract Number AAA 888888 BBB 888088 BBB 888333 What I could like to do is to write a formula or a macro to combine all the contracts numbers for a company into a single cell for example AAA 888888 BBB 888088, 888333 Can this be done? Thanks Pls try this formulae in column C =A1&" "&B1 Note : " " is for spacing Rajkuma -- Message...

MONEY RUNS SLOWLY
I cannot scroll through my account registers anymore when entering transactions. It seemingly takes forever for MONEY to scroll from one field to the next field. For some reason, my MONEY is runningly exhorbitantly slowly!!!!!!!!!!!! How can I speed it up?? thanks, larry You don't mention any useful information like version or what changed in your environment coincident with the decreased performance. Generally, see http://www.bollar.org/msmoney/#Q4. "FL Kulchar" <flk2575@comcast.net> wrote in message news:042c01c3d7be$d2c1cc20$a001280a@phx.gbl... > I cannot sc...

macro for reading pagenumber of a Document ?
Hi all, i am trying to write macro (MS excel) which has to read the page number of a comment present in the MS Document.i am able to read the number of sections and the number of pages in each sections.but how to read the comments pagenumber can anybody guide me. Thanx Manu You may want to ask this question in one of the MSWord newsgroups. If you have trouble implementing any suggested code in Excel, post back with that suggested code. Manu wrote: > > Hi all, > i am trying to write macro (MS excel) which has to read the page number of > a comment present in the MS Documen...

Printing multiple sheets from a macro
How do I print multiple sheets from a macro. I don't want to select them individually and print them individually, but rather choose multiple sheets and print them with one print call so they end up as one print job... Thanks Taylor Try Sheets(Array("Sheet1", "Sheet3")).PrintOut 'all sheets in the array ActiveWindow.SelectedSheets.PrintOut 'print all selected sheets -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Taylor Francis" <etfjr@yahoo.com> wrote in message news:401A70C4.2070401@yahoo.com... > How...

How can I insert current date into Word table cell?
In Access and Excell, one can use the "Ctrl;" or "Ctrl Shift ;" to insert the current date and time into a field. how can I do the same in an MS Word table? Use a { DATE } field. Insert>Fields -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "tmullis" <tmullis@discussions.microsoft.com> wrote in message news:0793051B-A3F8-43BF-8355-1B5E3F0BF074@microsoft.com... > In Access and Excell, o...

Macro help needed... #2
New to VBA, I'm struggling to do the following in a worksheet:- For all the cells in the sheet; Begin. If the cell.interior.colorindex is 1 (black) or 15 (light grey), do nothing except move on to the next cell and go back to Begin. If the cell value has anything other than 2 or 3 characters or letters, do nothing except move on to the next cell and go back to Begin. Otherwise, using Select Case, set the background color to an index linked to a particular string in the value, except that if the 2-or-3 character value is not found in the case list, do not change the backgroun...

Cannot update
Platform WinXP SP3 When I try to pull down updates from MS sites a window comes up saying that these sites ate only for WINDOWS not MAC. ie. somewhere along the line my machine is being read as a MAC machine. I only have as above, no second OS etc, but for a couple of months I cannot pull down updates. Dave On Sat, 24 Apr 2010 18:38:38 +1000, "David" <dtmail@bigpond.com> wrote: > >Platform WinXP SP3 > >When I try to pull down updates from MS sites a window comes up saying that >these sites ate only for WINDOWS not MAC. ie. somewhere alon...

Payroll Tax update
How do I grant rights to DYNSA to do a payroll tax update? For some reason 'sa' is not a user and we have been unable to add it as a user. We are unable to update our payroll taxes. Any help with this would be greatly appreciated. Thank you. Tracey D ...

Scheduling Workbook
Kind of an awkward situation really. My boss has asked me to set up a worksheet that allows him to "color in" the cells corresponding to the shifts that workers request/will work. I did so by giving him a legend to copy from that has text in each item that matches its designated color. In any case, what I'm hoping to accomplish, is to some how translate these 'x' cells into an actual read out of the time that they will work. In other words, if someone works 12 am - 8 am, he would use place the "color" in those 8 cells, essentially giving just those 8 cells a...

Saving current row on Form exit
I wish to save the current row on a form so that it can be automatically selected the next time the form is opened. To do this, I have the following - 'save the current row Private Sub Form_Close() SQLString = " DELETE * FROM tblPreviousFormState;" CurrentDb.Execute SQLString, dbFailOnError SQLString = "INSERT INTO tblPreviousFormState(TableID) Values(" & TableID & ");" CurrentDb.Execute SQLString, dbFailOnError End Sub I'm sure there is some nice way of having a one row table but I'm not sure about this... TableID is th...

linking data with other workbooks
Hello, I have a spreadsheet (FrontEnd.xls) whos cell gets data from another spreadsheet using (='C:\junk\[BackEnd.xls]Sheet1'!C3). I then close both spreadsheets and open BackEnd.xls and insert a column causing the BackEnd data to now be C4. Now when I open the FrontEnd.xls I am asked if I want to update the links. After choosing "Yes", the cell is still pointing to "'C:\junk\[BackEnd.xls]Sheet1'!C3" rather then to C4. Is there a way to have the FrontEnd.xls spreadsheet update cell references that have shifted because of insertions or deletion of...

Unwanted Workbook Opening
When I open a particular workbook and make changes, I sometimes notic that another, unrelated workbook is open. When I just open the desired workbook and check before doing anything I find that the unrelated one is not (yet) present. I'm not using an autoopen macro, and there are no references in th desired workbook to the unrelated one. I've not been able to determine what actions (if any) trigger th unwanted opening, partly because the phenomenon appearst o b intermittent and I forget about it until it occurs again. I'm using Excel 2003 (11.6355.6360) SP-1 and the operating...

Cell Format #8
Nope, didn't want to do that either (it's not actually my workbook). The best method may be to set the word wrap and then set the column height. ...

Macros in 2003
Is it true that file created in Excel 2000 having Macros will not work on Excel 2003. We are facing problem with Excel 2003 with the files which are having Macros and these files were created on Excel 2000. Because of this problem I need to go back to Excel 2000. Anis -- MCSE & CCNA Hello Anis First of all, did you check the macros' security level in Excel 2003? This level must be set to Low or Medium to allow macros'execution. HTH Cordially Pascal "Mohd Anis" <MohdAnis@discussions.microsoft.com> a �crit dans le message de news: FD408CD8-357D-40F9-A97F-3F86...

Calculating a value but omitting cells with empty data
I want to calculate a value based on several cells in say row 4. However, I do not want to include values in any columns that do not also have a non-missing value in, say, row3. Thus my calculated cell in, say column A, should have some syntax like: IF A3 <> MISSING THEN < do calculation of value> I hope this is clear. What is the proper syntax for the pseudocode that I have above? Thanks! Depends on what your calculation is. Are you summing, counting, multiply, etc.?Each has their own formula structure. For instance, the basic summing one is =SUMIF(3:3,"<>&...

Opening "Save As" Dialog Box Corrupts Headers/Footers
Why do my headers/footers change/get erased when I open Save As diaolog box in Microsoft Word 2007 on Windows computer? Here's what I am doing: 1. I open a word 2007 document and then I do a Save As to save the file under a different name. 2. I can see my document in the background while the dialog box is open and I noticed that the header text I had got replaced with a single word and my footer page count vanished. 3. If I complete the Save As, the new document has the modified header/footer text This does not make sense...what am I doing wrong?? Bob What fields d...

fixing or freezing cell links
I have a multi-sheet workbook in which forumlas in one sheet link to cells on subsequent sheets. When I insert a column in one of the referred to sheets the forumlas automatically change to the "new" location (e.g. C25 become D25). Is there a way to prevent the forumlas from changing as I insert columns? Hi try =INDIRECT("'sheet1'!C25") >-----Original Message----- >I have a multi-sheet workbook in which forumlas in one sheet link to cells on subsequent sheets. When I insert a column in one of the referred to sheets the forumlas automatically change to the...

Macros Wont Run After Hiding Sheet
Hi I run Excel 2K I have a table on a wroksheet called SUPPORT SHEET. I use the sheet for various purposes. eg, validation tables, lookup tables etc On a different sheet I have a macro that copies data from the SUPPORT SHEET. The macro works fine until I hide the SUPPORT SHEET, then the macro does not work. Is there a way I can hide the SUPPORT SHEET but the macro I use still work? Thanks John Two options: Sub test() Application.ScreenUpdating = False Worksheets("SUPPORT SHEET").Visible = True 'yourcode Worksheets("SUPPORT SHEET").Visible = False Applicat...

Hide Cell Row and column number
is there a way to hide the cell info on the far left and far top of the sheet? I can make the screen full size and hide the top info, but how do i get rid of the 1,2,3,4,5,... on the left side of the screen? -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4918 View this thread: http://www.excelforum.com/showthread.php?threadid=489791 Click >Tools >Options and then select the View tab. De-select the "Row and Column headers" checkbox. ...

Running TZUPDATE on Resource Mailboxes
Had a thought - what if I were to log in to each resource mailbox in outlook and run the TZupdate tool to move meetings during the new DST rules. Would that send updates to all attendees for that appointment/resource? I was thinking it would be better/easier than going to each user mailbox. They would still have to run the TZUPDATE to move all appointments, but it might save the hassle and frustration of showing up for meetings at the wrong time or when a room is full of people already. Thoughts? Thank you On Thu, 8 Mar 2007 03:31:02 -0800, TheITDude <TheITDude@discussions.microsoft...

Save & Save As features in file menu of Excel
The save button and both "save" & "save as" options in file dropdown menu are not highlighted in Excel 2003. How do i restore the capability to save my work in Excel. Only way I can do it now is to close the program and wait for a prompt to save the file. I double checked to see if any features were inadvertently disabled on the "About Microsoft Excel" help tab, nothing was listed. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany Blue wrote: > The save button and both "save" & "save as" options in file dro...

Using saved query for different tables
I want to use the same query for many tables. I'm new to Access. I see where you can save the query and name it, but is there a way to then use it with different tables? I can't see where that can be done. This would be most helpful in Update Queries. -- Barry Barry: You could build the UPDATE statements in code in a dialogue form's module, getting the variable table names from unbound controls on the form, and then execute the statement. You could even modify the SQL property of a saved query in code and then execute it. The fact that you are contemplating thi...

Excel: Remove characters from cells using wildcards
Can you anyone help me with this problem please? I have a column of data like the one below and I would like to remove the the front part, (x) from the cell, but I don't want to remove the parts in bracket that come later in the cell, for example (Queensland). I tried using Replace (**), and it did remove the first set of brackets and its contents but unfortunately it also removed the second set of brackets and its contents. If it helps in the first set of bracket, (x), x is always a number . While in the second set always contains a word. Before: (1) ABC Far North (Queensland) 0630 New...