Run Code On Open Spreadsheet

[Hack]

I have some code that I would like to run each time a specific worksheet is opened in Excel.

What is the spreadsheet equivalent to Form_Load?

[Siddharth Rout]

For worksheet it is

Private Sub Worksheet_Activate()
    MsgBox "Hi Hack. You have selected a sheet"
End Sub

For workbook it is....

Private Sub Workbook_Open()
    MsgBox "Hi Hack"
End Sub

Hope it helps...

[Hack]

Are these built in Subs or do I have to create them myself?

[zaza]

I presume you mean Workbook_Open - it's built in and you'll find it when you go into the ThisWorkbook object from the Project window.
Worksheet_Activate will fire whenever you select a particular worksheet in a book.

[Siddharth Rout]

They are built in.

In an Excel sheet press Alt=F11 to open the VBA Editor then double click the relevant Sheet to open the code window.

In 'General' select the sheet and in "declaration" select the event

Hope this helps...

[Hack]

Cool!

That code that you posted on clearing out the textboxs got copied to Worksheet_Activate so it does it when the spreadsheet is opened.

Now all I have to do is figure out a way to force them to do a SaveAs rather than a Save (which continously overwrites the template spreadsheet that I've been asked to do some things with), life will become a little easier.

[Siddharth Rout]

Cool!

That code that you posted on clearing out the textboxs got copied to Worksheet_Activate so it does it when the spreadsheet is opened.

Now all I have to do is figure out a way to force them to do a SaveAs rather than a Save (which continously overwrites the template spreadsheet that I've been asked to do some things with), life will become a little easier.

Hi there are two ways of doing it...

1) Without Code.

When you save the intial file, in the "save/save as", under the "Tool options" click on "General options". In the "Save options", type the password for "Modify" and put a "check" on the box which says "Readonly". This will make sure that the users will always have to save a copy.

2) With Code (Try recording a macro for the above steps)

Replace "P:\temp.xls" in the below code with the respective filename "abcde" with a password of your choice...

Sub Macro1()
ActiveWorkbook.SaveAs Filename:="P:\temp.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="abcde", ReadOnlyRecommended:=True, _
        CreateBackup:=False
End Sub

Hope this helps...

[Hack]

Hi there are two ways of doing it...

1) Without Code.

When you save the intial file, in the "save/save as", under the "Tool options" click on "General options". In the "Save options", type the password for "Modify" and put a "check" on the box which says "Readonly". This will make sure that the users will always have to save a copy.

I don't understand this one. I can't find the Options you are referring to.

[Siddharth Rout]

It comes when you click on Menu File=>SaveAs

[Hack]

But, that doesn't stop them from doing a straight "Save".

[techgnome]

Can the readonly property be set via code? If everytime the workbook opens it marked itslf as readonly, surely that would be enough to cause Excel to do a SAve As even if they hit the save button.

-tg

[Hack]

Can the readonly property be set via code? If everytime the workbook opens it marked itslf as readonly, surely that would be enough to cause Excel to do a SAve As even if they hit the save button.

-tg

I like this......is this possible?

[techgnome]

I imagine so... I remember years ago in a former life when I had to open Word files, turn off the QuickSave option then Save As a new file.... given that that was possible, I imagine this should be as well.... Try recording a macro and then go through the steps of setting the flag.... see what it generates.

-tg

[Siddharth Rout]

But, that doesn't stop them from doing a straight "Save".

yes it does

When you check the "readonly" option and save the file, you need to close it once. next time you open it, it will prompt you to open the file in "readonly" mode.... and until and unless you feed in the password it won't let you overwrite the file....

[Hack]

I've gone through all of the menu options in a spreadsheet and I can't find a QuickSave option to turn off.

[Hack]

yes it does

When you check the "readonly" option and save the file, you need to close it once. next time you open it, it will prompt you to open the file in "readonly" mode.... and until and unless you feed in the password it won't let you overwrite the file....

Ohhhh...I didn't close it first, I just hit "Save"

Let me try that.

[techgnome]

I've gone through all of the menu options in a spreadsheet and I can't find a QuickSave option to turn off.

That was a WORD option from back when.... I was simply saying that if I could change that using VBA back in word95, then surely they option you're looking for exists in the same way...

-tg

[Hack]

That was a WORD option from back when.... I was simply saying that if I could change that using VBA back in word95, then surely they option you're looking for exists in the same way...

-tg

Ah, Word. Ok, I'll keep snooping around Excel.

[Siddharth Rout]

I imagine so... I remember years ago in a former life when I had to open Word files, turn off the QuickSave option then Save As a new file.... given that that was possible, I imagine this should be as well.... Try recording a macro and then go through the steps of setting the flag.... see what it generates.

-tg

Hi tg

The Quicksave option that you are talking about i feel is (I could be wrong) is the same as "fast save" which is entirely a different concept...

[techgnome]

Sigh.... I know that.... I was using it as an EXAMPLE..... record a macro, do the actions you want to code.... then read the macro, look at the code.... convert it to VB or stuff it into your VBA code, with the appropriate properties. MY POINT was that if I could programmatically change the FastSave (quick save, what ever) OVER 10 years ago, then it SHOULD be a simple and easy thing to do in Excel for the ReadOnly option.

Maybe I should just stay out of the Office Development forums, trouble seems to follow me...

-tg

[Hack]

Actually, this looks promising.

[Siddharth Rout]

But this isn't what you wanted???

Did you try the readonly option?

[Hack]

No, not yet....I got called away to "put out some fires"

I'll give it whirl.

[Hack]

That actually seems to work fairly well.

I saved it and rolled it out the users production area.

Lets see what they think.

[westconn1]

the other option is to save it as a template, then users will open a new book based on the template

[Siddharth Rout]

That actually seems to work fairly well.

I saved it and rolled it out the users production area.

Lets see what they think.

That's the best way to check it