PDA

View Full Version : ABOUT SAVE



jinvictor
06-20-2006, 07:02 AM
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Worksheets("Sheet1")
.Unprotect
.UsedRange.Value = .UsedRange.Value
.Protect
End With
End Sub

i try to use this VBA to avoid other people to reuse the save as file, what i want is people can use the protected workbook, but when they save as another name, all fomula is gone, the worksheets only show the numbers after calculation. now when i use this vba, i can save the original file. what shall i do?

lucas
06-20-2006, 07:10 AM
Hi jinvictor,
I'm afraid I don't understand your problem. Are you saying that you can't save the original file because it removes your formula's? Please clarify your problem....

jinvictor
06-20-2006, 07:21 AM
my problem is when insert this vba in my original workbook, i have to save it, when i reopen again, it block all the formula, so the original is not working any more.


Hi jinvictor,
I'm afraid I don't understand your problem. Are you saying that you can't save the original file because it removes your formula's? Please clarify your problem....

lucas
06-20-2006, 07:26 AM
I think the most widely advised method of working around this problem is to create an addin.

An alternative is what I usually do: I add this to the workbook open code(note that you will have to change the path to suit your needs). Then the user is working with a copy of the file and the original doesn't change.

Private Sub Workbook_Open()
ActiveWorkbook.SaveAs FileName:="F:\Temp\BOM.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Run "checkname"
End Sub

Note: you may wish to put your code to remove formula's etc. in something besides workbook open. beforeprint maybe.....hope this helps

jinvictor
06-20-2006, 07:39 AM
sorry im only new, what i really want is: i did a protected workbook, and i will give to other people by email or cd, what i want is every time they use it they only can use save as, when the new file saved, they will be no fomula but only the the data they put in, so they can reuse that saved workbook again, but it won't affect the original one, they still able to use it.
can you help me do a vba for that?
thanx


I think the most widely advised method of working around this problem is to create an addin.

An alternative is what I usually do: I add this to the workbook open code(note that you will have to change the path to suit your needs). Then the user is working with a copy of the file and the original doesn't change.

Private Sub Workbook_Open()
ActiveWorkbook.SaveAs FileName:="F:\Temp\BOM.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Run "checkname"
End Sub
Note: you may wish to put your code to remove formula's etc. in something besides workbook open. beforeprint maybe.....hope this helps

lucas
06-20-2006, 09:11 AM
This is a problem that I have had in the past....you want a workbook to do certain things and then you wish to remove all code and formula's.

In the attached workbook is the code that I posted earlier. It creates a copy of the file on open....then you run the code in the copy and at the end of the process(sort sheets in this example)you call the code to delete(in this case all code, formula's and sheet 1 after the other sheets are added) hope this helps

remember the path will have to be changed in the workbook open module

jinvictor
06-20-2006, 07:16 PM
sorry again, i can't download the file, can you upload again, thanx


This is a problem that I have had in the past....you want a workbook to do certain things and then you wish to remove all code and formula's.

In the attached workbook is the code that I posted earlier. It creates a copy of the file on open....then you run the code in the copy and at the end of the process(sort sheets in this example)you call the code to delete(in this case all code, formula's and sheet 1 after the other sheets are added) hope this helps

remember the path will have to be changed in the workbook open module

lucas
06-21-2006, 06:21 AM
Try right clicking on it and then select "Save Target As" if you have IE