change formula in a shared worksheet without losing change history

[DCE]

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.

 

[NoodNutt]

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.

 

[DCE]

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]

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) Tools>References 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.

 

[NoodNutt]

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

 

[DCE]

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.