update Link from protected wb OR prompt for protecting a worksheet

[Guest]

What I am actually trying to do is to link data from a protected workbook (so
a user can give a password in the save as > tools > general options menue)
into another workbook to consolidate different users information.

Unfortunately, the consolidation workbook only updates the data when you
type in the password of the data entry workbook (which makes the password
useless). Is there a way to link data with this protection mechanism?

If not, what would work for me is to use the protect worksheet function and
hide the columns before protecting it. My second question then is ...

Is there a way to write a macro doing the following:
- close a data group
- prompt a user to enter the data sheet protection password
- (probably you need to type it again to verify)
- save the document
- close the document

any help is greatly appreciated.
Andreas

 

[Dave Peterson]

I don't think you can avoid those password prompts when you hit refresh links.

But you could provide a workbook that opens all the workbooks that send values
(you supply the password in your code to open the workbooks), then open the real
workbook.

Then close all the other workbooks.

You can record a macro when you show/hide rows/columns to get the code.

And if you just want to allow the user to be able to expand/contract those
outlines:

If you already have the outline/subtotals applied, you can protect the worksheet
in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=====
In fact, with that userinterfaceonly:=true portion, your code should be able to
hide/show rows without unprotecting the worksheet.