Solved: Force save different name on WB open or save

[blackie42]

Picked up the following code to force user to save on opening

Private Sub Workbook_Open()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Problem is it allows user to overwrite.

What I want to do is make sure the blank template isn't overwritten either on opening or saving.

Any help appreciated
thanks
Jon

[GTO]

What I want to do is make sure the blank template isn't overwritten either on opening or saving.


Hopefully not too obvious of a question, but is the template saved as a template (ie .xlt)?

Mark

[blackie42]

Hi GTO,

It wasn't saved as a template but is now.

Does the problem still not exist i.e. can overwrite the template?

thanks

[GTO]

Hi Blackie,

If you are installing the template in the template's folder there is less likelihood, as they would be using File|New.

I am betting though that it is rather the case that you are just putting one copy on a network drive? In this case, you are quite correct, as if it is being accessed thru Windows Explorer, there will be those who right-click and select Open (even though New is bolded).

Presuming that the users are doing this only thru lack of knowledge, the question becomes, do you trust them to have macros enabled.

If yes, you could try this in the workbook open event:


Option Explicit

Private Sub Workbook_Open()

If Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - _
InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare)) = "xlt" _
And Not UCase(Environ("USERNAME")) = "MARK" Then
MsgBox "When ""opening"" {your template's name} through Windows Explorer," & vbCrLf & _
"either double-click the file, or right-click and select <New>." & vbCrLf & _
vbCrLf & _
"You CANNOT open the template itself, as this will overwrite it...", vbCritical, ""

ThisWorkbook.Close False
End If
End Sub


Now this code will be saved with the new wb created, but no harm, as it won't run once the extension is xls.

Depending on how many will access the template, another way is if you can send users an email - to wit: "I have fixed the template, please click the link to install a shortcut to it..."

Have the link go to a vbs file that plops a shortcut to the template on their desktop. As long as they use the shortcut (which a lot of casual users like better than explorer anyways), the template will not be botched up, and you don't have to have macros in it.

Hope that helps,

Mark

These are certainly not a perfect solution, and maybe others will toss in what they have found most successful?

[mdmackillop]

An alternative is to use the BeforeSave Event to append the Date and Time to the filename. A little more complex is to search for occurrences of the filename and add an incremental number.
These don't require additional user input.

[blackie42]

Thanks for replies guys,

Have decided to give each user a copy and lock cells/VBA code. If they overwrite anything on the spreadsheet they'll just have to clear it all down before starting again.

regards
Jon