Macro Security & Automatic Link updates

Greetings Excel Gurus!

I have a set of spreadsheets organized in a hierarchy which all link to the spreadsheet above and below in the chain. These spreadsheets also all contain macro's for 'automated' data input from the user.

I have two questions/requests.

1- Is there a way to avoid the Security Warning due to macro's? I have to instruct all my users to drop the macro security to "medium" and select "enable macros" each time the user opens the file. Is there any way to avoid this?

2- Is there a way to avoid the pop up box requesting if you want to update links or not? Frankly, a way that it would automatically pick to update the links would be perfect.

Thanks for your time! And also, I don't consider myself fully 'proficient' with Excel just yet, infact previous to this project I didn't really do any extensive VBA work at all. So please, provide as much detail and explination as you can with any answers. Thanks again!

Good morning

1. There are two ways of going about this. The easy way is to set your macro security to low - not recommended, but you did ask! The other way is to set yourself up as a trusted source with a digital signature and have this validated by Excel in the VBA environment (Alt + F11, Tools > Digital Signature...).

One other way, that may or may not be acceptable depending on the set up of your spreadsheets, is to have the data entry code set up as an add-in. All users have the add-in installed on their machine locally, and your spreadsheets call it when they start.

2. Your second question is little easier. Go to Tools > Options... under Edit tab uncheck Ask to update automatic links. This is a general Excel setting, so it will not ask about updating the links in ANY file a user opens - you might want to think carefully about this. Or you could set up an auto_open macro (or a workbook_open module) that turns it off on opening and back on again on closing using the code:

Application.AskToUpdateLinks = True

and

Application.AskToUpdateLinks = False

HTH

DominicB

Hello DominicB, thanks for your reply!

I went to Tools >> Digital Signature from VBA, and I didn't have any signatures to choose from when I attempted to choose one. How can I get a digital signature? I need all the help I can get with this one.

FOr my 2nd problem, those commands are exactly what I wanted. That will work perfectly. The only problem is, I don't know how to implement them to the code. Will I need a 'onLoad' or 'onUnLoad' command before them? Or will placing them at the top and the bottom of my VBA code suffice?

Thanks again for your time!

Hi PH8 (that can't be your real name can it...?)

Thanks for your feedback - it's always nice to hear what users think of suggested ideas. As for your questions, well...

I know the theory behind digital signatures but have never actually got one myself, but for a start try the Microsoft site, which gives you a good idea of how to go about getting one:

http://office.microsoft.com/en-us/as...446121033.aspx

I have been thinking a little about the code aspect, and we would have to turn off the "update asking" bit before opening the first spreadsheet. Now if there is always one sheet that has to open first (say MySheet.xls) then we could use a separate sheet that is opened first which turns off the update? question, and opens the MySheet.xls file and leaving that file on top

Sub auto_open()
Application.AskToUpdateLinks = False
Workbooks.Open Filename:="C:\MySheet.xls"
End Sub

Obviously, you need to substitute the full path in there.

I have used auto_open for ultimate compatibilty because I don't know what version of XL you are using. To insert this code press ctrl + F11, Insert > Module and paste the code in the window. This code will run when the spreadsheet opens.

You would also need this code in whatever is to be the last file to be closed. This would turn the questions? option back on.

Sub auto_close()
Application.AskToUpdateLinks = False
End Sub

This will automatically run when the worksheet containing it is closed.

Hope that answers your questions - shout out if you need any more info and I'll do what I can.

DominicB

Hello Dominic, and no, it isn't my real name . Eddie is though!

Anyways. So what you are saying is there is no way to disable the ask to update links box when opening a spreadsheet directly? I don't have a 'master' spreadsheet opening up all the subsequent spreadsheets. They are all in a set of directory hierarchy and to open them the users are instructed to just double click the icon for their respective level.

I thought I remember reading at one point a type of action like 'onLoad' or onOpen or something. To signify when the file is being opened. The same I thought exists for closing the document as well.

In essence though, all this would serve is simplicity. The code and spreadsheets are still fully-functional, just involve more clicks.

I do have another 'link' related question. Currently, if I set up a link to another spreadsheet, if that spreadsheet has nothing in the cell I want the data to be pulled from, a zero (0) appears in the sheet I am linking to. Is there a way to avoid this?

Thanks again for all your help Dominc.

v/r
Eddie H

Good morning Eddie (so why PH?????)

First of all your onOpen question:
The macros I supplied were called auto_open and auto_close : they execute on the workbookopening and closing. You can also do it using event procedures (Workbook_Open and Workbook_BeforeClose) - is it these you are thinking of?

The only way to access and change an Office control is to open the program. You could add this line before the End Sub command of the auto_open macro:

Application.WindowState = xlMinimized

This will minimise the XL xcreen just opened - allowing the user to see the desktop, which is where I assume the users click on the icon to open. I cannot find an event that will allow the questions? control to be switched off before the recalculation takes place so you have a decision to make here. Do you request users open an extra "hidden" spreadsheet, or do they have to say "yes" to the recalc question?

Finally, your zero question : Tools > Options, View tab has an option, Zero values. This option will suppress any field that has a zero showing it as blank. To access it with VBA use:

ActiveWindow.DisplayZeros = False

(or True).

Hope all this helps.

DominicB