Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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...o&userid=31190 View this thread: http://www.excelforum.com/showthread...hreadid=508603 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Run Macro(save workbook) after cell updated
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link A Cell In O ne Workbook To A Formula In Another Workbook | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
linking cell value in one workbook to a cell in another workbook | Links and Linking in Excel |