ABOUT SAVE

[jinvictor]

[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Worksheets("Sheet1")
.Unprotect
.UsedRange.Value = .UsedRange.Value
.Protect
End With
End Sub
[/VBA]
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]

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]

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]

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

[jinvictor]

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

[lucas]

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]

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]

Try right clicking on it and then select "Save Target As" if you have IE