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!


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=345781

Macro Security & Automatic Link updates
 

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=345781

Macro Security & Automatic Link updates
 

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!


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=345781

Macro Security & Automatic Link updates
 

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=345781