Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 23rd 08, 06:46 PM posted to microsoft.public.excel.worksheet.functions
DCE DCE is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 5
Default 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   Report Post  
Old July 24th 08, 01:13 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 221
Default 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   Report Post  
Old July 24th 08, 02:02 PM posted to microsoft.public.excel.worksheet.functions
DCE DCE is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 5
Default 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   Report Post  
Old July 25th 08, 03:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 221
Default 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 = "[email protected]" '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   Report Post  
Old July 25th 08, 03:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 221
Default 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   Report Post  
Old July 25th 08, 01:37 PM posted to microsoft.public.excel.worksheet.functions
DCE DCE is offline
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 5
Default 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 = "[email protected]" '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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to change formula in shared sheet without loss of change histo DCE Excel Worksheet Functions 1 July 23rd 08 05:09 PM
How to change the default of 30 days change history Andy Smith[_2_] Excel Discussion (Misc queries) 6 October 23rd 07 11:57 PM
How change days of change history tracking? DonSpmp Excel Worksheet Functions 3 August 25th 06 01:13 AM
cant not change history on a shared workbook cyndi Excel Discussion (Misc queries) 0 December 15th 05 09:14 PM
change history with no [shared] file name Murf2 Setting up and Configuration of Excel 3 October 21st 05 01:50 AM


All times are GMT +1. The time now is 11:30 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017