Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change formula in a shared worksheet without losing change history
Hi Rick,
Thank you for calling my attention for the lack of details with my question. My question is: I posted a shared worksheet in a shared drive that others could update with their changes and corrections. However, getting back to the shared worksheet, I realized I had to change an array formula in one of the tabs. When I tried to change the formula, I receive a message saying that it is one of the things that I am not allowed to do without losing the change history ( records of people's ID, time & date they went to the file and the changes they made). One way I thought of doing is to save the old file in a place others do not have access to, & re-create the file with a new file name. That will allow me now to change my formulas. However, I will lose the change history in the new file and I do not want that. I know that all the changes are there but I want to be able to see the change history just in the one new file without having to bring out the previous file. I hope this clears my dilemma. Thank you Rick for your suggestion. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change formula in a shared worksheet without losing change history
G'day
I would be inclined to remove the Save button from the menu bar/ribbon and replace it with a Save cmdbtn using code to save to a specific location and file extension. You could have multiple saving of the same file, saved with a time extension, this way each time the file is saved, you can track the differences between each sheet. Try this, it should do the trick (not tested) Dim Sourcewb As Workbook Dim TempFilePath As String Dim TempFileName As String Set Sourcewb = ActiveWorkbook TempFilePath = C:\Where-Ever-You-Want-It TempFileName = Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") With Destwb .SaveAs TempFilePath & TempFileName On Error Resume Next On Error GoTo 0 .Close SaveChanges:=False End WithHTHRegardsMark. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change formula in a shared worksheet without losing change his
Hi Noodnutt,
I understand your suggestion to do multiple savings with time extension conceptually because that's what I have been doing in a very manual way, but I cannot follow the "program" ( ??) you wrote that I believe is intended to automate capture of all changes from start to current file?? How do I integrate that into my worksheet? Thank you very much for your time. - DCE "NoodNutt" wrote: G'day I would be inclined to remove the Save button from the menu bar/ribbon and replace it with a Save cmdbtn using code to save to a specific location and file extension. You could have multiple saving of the same file, saved with a time extension, this way each time the file is saved, you can track the differences between each sheet. Try this, it should do the trick (not tested) Dim Sourcewb As Workbook Dim TempFilePath As String Dim TempFileName As String Set Sourcewb = ActiveWorkbook TempFilePath = C:\Where-Ever-You-Want-It TempFileName = Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") With Destwb .SaveAs TempFilePath & TempFileName On Error Resume Next On Error GoTo 0 .Close SaveChanges:=False End WithHTHRegardsMark. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change formula in a shared worksheet without losing change his
First things first !
Credit: The enclosed code is a compilation of codes pieced together. The code(s) come from Ron DeBruins website. DCE As I suggested, this is how I would do a work around.. Place a 'Save' command/Macro button on the worksheet, so everytime whoever is using it and wants to save their version of what they have done, whether altered/edited/amended it will be saved using that macro. Add an additional code that would alert you via e-mail the instance it is saved, then you could go directly to the freshly saved file and compare/amend as to your requirements so that when he/she goes to use it at a later date you will have incorporated any changes that you or other people may have done, then, if you like, you could save the amended file as the original file so that when other user access it, it will be up to date. It may be a case whereby you advise users that once they have saved, they can't access it again until a timeframe to which you feel comfortable, hence giving you the time you need to do whatever it is you need to do before releasing it with other information from other users, (this assumes no-one else is using it at the time). I hope that makes sense, or it is something that will help. Try something like this in the macro behind your newly created 'save' button Sub Save_As_MyTempAccessFile() Dim SourceWB As Workbook Dim DestWB As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim SaveAns As Integer Dim SaveResp As Integer Dim CancelSave As Integer SaveAns = MsgBox("Are you sure you wish to save your changes and end your session??????", vbYesNo) If SaveAns = vbYes Then Set SourceWB = ActiveWorkbook Set DestWB = ActiveWorkbook TempFilePath = "C:\Where the file will be saved" TempFileName = SourceWB.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") ' or whatever mail program you are using - works best with MS Outlook & Express OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With DestWB .SaveAs TempFilePath & TempFileName On Error Resume Next With OutMail .To = "Me@MyLocation" 'enclosed in double quotes .Subject = "File Save As Update" .Attachments.Add DestWB.FullName .Send End With On Error Resume Next End With Set OutMail = Nothing Set OutApp = Nothing SaveResp = MsgBox("Your file has been successfully saved, your session will now end", vbOKOnly) Application.ActiveWorkbook.Close Else CancelSave = MsgBox("You have chosen to continue working", vbOKOnly) Cancel = True End If End Sub Note: You must add a reference to the Microsoft outlook Library 1) Go to the VBA editor, Alt -F11 2) ToolsReferences in the Menu bar 3) Place a Checkmark before Microsoft Outlook ? Object Library ? is the Excel version number Replace this three lines in the code Dim OutApp As Object Dim OutMail As Object Set OutMail = OutApp.CreateItem(0) With this three Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutMail = OutApp.CreateItem(olMailItem) Don't forget to disable the save button on the menu ribbon so the users can't save that way. I hope this helps, if it's not quite what you are looking for, hang in there, one of the many MVP's may have a better solution for you. Regards Mark. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change formula in a shared worksheet without losing change his
DCE
This code will check if a file exists and or is open, that way you will know it is active before you start to do any modifications to it before the user saves it. HTH Mark. http://www.rondebruin.nl/exist.htm |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change formula in a shared worksheet without losing change his
Hi Noodnutt,
Thank you so much. I read your suggestion and I find that it is way over my simple needs. You surely tweaked my interest to learn excel macro now. "NoodNutt" wrote: First things first ! Credit: The enclosed code is a compilation of codes pieced together. The code(s) come from Ron DeBruins website. DCE As I suggested, this is how I would do a work around.. Place a 'Save' command/Macro button on the worksheet, so everytime whoever is using it and wants to save their version of what they have done, whether altered/edited/amended it will be saved using that macro. Add an additional code that would alert you via e-mail the instance it is saved, then you could go directly to the freshly saved file and compare/amend as to your requirements so that when he/she goes to use it at a later date you will have incorporated any changes that you or other people may have done, then, if you like, you could save the amended file as the original file so that when other user access it, it will be up to date. It may be a case whereby you advise users that once they have saved, they can't access it again until a timeframe to which you feel comfortable, hence giving you the time you need to do whatever it is you need to do before releasing it with other information from other users, (this assumes no-one else is using it at the time). I hope that makes sense, or it is something that will help. Try something like this in the macro behind your newly created 'save' button Sub Save_As_MyTempAccessFile() Dim SourceWB As Workbook Dim DestWB As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim SaveAns As Integer Dim SaveResp As Integer Dim CancelSave As Integer SaveAns = MsgBox("Are you sure you wish to save your changes and end your session??????", vbYesNo) If SaveAns = vbYes Then Set SourceWB = ActiveWorkbook Set DestWB = ActiveWorkbook TempFilePath = "C:\Where the file will be saved" TempFileName = SourceWB.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") ' or whatever mail program you are using - works best with MS Outlook & Express OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With DestWB .SaveAs TempFilePath & TempFileName On Error Resume Next With OutMail .To = "Me@MyLocation" 'enclosed in double quotes .Subject = "File Save As Update" .Attachments.Add DestWB.FullName .Send End With On Error Resume Next End With Set OutMail = Nothing Set OutApp = Nothing SaveResp = MsgBox("Your file has been successfully saved, your session will now end", vbOKOnly) Application.ActiveWorkbook.Close Else CancelSave = MsgBox("You have chosen to continue working", vbOKOnly) Cancel = True End If End Sub Note: You must add a reference to the Microsoft outlook Library 1) Go to the VBA editor, Alt -F11 2) ToolsReferences in the Menu bar 3) Place a Checkmark before Microsoft Outlook ? Object Library ? is the Excel version number Replace this three lines in the code Dim OutApp As Object Dim OutMail As Object Set OutMail = OutApp.CreateItem(0) With this three Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutMail = OutApp.CreateItem(olMailItem) Don't forget to disable the save button on the menu ribbon so the users can't save that way. I hope this helps, if it's not quite what you are looking for, hang in there, one of the many MVP's may have a better solution for you. Regards Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change formula in shared sheet without loss of change histo | Excel Worksheet Functions | |||
How to change the default of 30 days change history | Excel Discussion (Misc queries) | |||
How change days of change history tracking? | Excel Worksheet Functions | |||
cant not change history on a shared workbook | Excel Discussion (Misc queries) | |||
change history with no [shared] file name | Setting up and Configuration of Excel |