Workbook_open() to remind to Save As...

[I/ATech]

I often make templates of forms for weekly, bi-weekly, monthly, etc., forms for others to use and I tell them to be sure to use Save As... with a new name in order to keep the original form intact. We do not have a common location for templates here. I have tried a Workbook_open() macro to give them a reminder message, but the macro gets saved with the new file, which asks them again to use Save As..., which they shouldn't do.
Is there a way to delete the macro from the new file? Or some other method to acheive the goal?
Thank you in advance.

 

[Norie]

So you couldn't just point out that if they double click a on the desktop for the template they will create a new file.

And they won't need to goto Files>Open... and navigate to the file.

 

[I/ATech]

Yes, I can and I will.
In the past I have asked some to do things certain ways, such as saving files in a certain naming convention, and within a short period of time been disappointed with the results. I even have a message in a cell that explains how to save, in what format and to delete the contents of the cell afterwards. At the end of the period the forms are turned in, hard copy, and some will still have the message in the cell. Kind of embarrasing to send that to enforcement agencies.
I am very interested in becoming profficient in programming in Excel and will eventually figure out how to name the files for them, but I have to learn that time-permitting (i am a technician, so doing these things are appreciated, but not really in my area of responsibility).
In the meantime, I will tell them how to open and save their forms and also add a feature that, if they mess up, gives them a gentle reminder.
Sorry if I violated decorum with this long message.

 

[lenze]

You might solve the Deleting of the warning with a BeforeSave Event

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet1.Range("$A$1").ClearContents
End Sub

But I might use a UserForm instead of a cell (You probably can make it self-destruct ala Mission Impossible)

lenze

 

[I/ATech]

I love how you guys comeup with elegant and simple solutions. If the UserForm is what I think it is, I will probably go that route. I'll do some studying.
Thanks again!

 

[Norie]

I/A Tech

You've not violated any rules, in fact sometimes a long explanation is better than a couple of words, no data and an ambiguous question.

I think we can all sympathise with your situation.

What are you actually thinking about when naming the workbooks?

Some sort of user input?

I'm sure if you asked nicely somebody might find something of use hiding in the recesses of their sleeves.

One idea I had was to create some sort of user interface (UI), perhaps a workbook with a userform which lists the templates.

User picks template they want, hit's button, get's asked for filename, file name verified, new file saved and UI workbook closed.

They can then work on the new file.

PS you mentioned 'enforcement agencies' -surely they'll only accept information in the required format. Might be an idea to send a memo around about that.:wink:

 

[I/ATech]

Norie,
One example is a workbook with a sheet for each operating area in which we track results from semi-monthly calibration and tests of numerous toxic gas monitors. This is submitted hard-copy monthly and reviewed electronically periodically. These are named as YY-MM-MonitorLog.xls so that they sort chronologically when listed, but that is not a requirement.
As far as agency reports, you are correct, but these are not official permit-required reports, merely forms to attest that the inspection was done; in the view of attorneys, if you didn't log it, you didn't do it.
Another thing I did was to bring our time reporting from the 19th century into the 1980's. Each technician fills out their timesheet then emails it to the timekeeper. The workbook has one sheet with two week's worth of time, another sheet with all the federal and State tax tables and on a separate sheet has a likeness of their pay stub. Come payday, if it's not within a nickle its time to call the office. I am looking forward to bringing this process into the 21st century as I learn more.
Your idea of the User Interface sounds intriguing. I started learning about User Forms via this forum and Excel and VB Help and am hooked on learning more. I am going to look at the resources listed here at Mr. Excel, so if anyone has a book suggestion for a programming novice I'd be delighted.
I hope my brain doesn't explode while I am having all this fun.