I am new to macros, not to excel. I have a recap worksheet that gathers
information from various password protected worksheets. When I update the
recap worksheet, I have to enter each password every time before the data is
updated. How do I automatically have the passwords entered, either through a
macro or a formula?

You don't need to unprotect worksheets to collect data from them.

What gathering are you doing that requires the unprotecting of the sheets?

Explain a little more the process you are performing.

If you do have to unprotect the multiple sheets for some reason like filtering
or sorting it would be best to have the same password for all sheets.

Easier to unprotect and reprotect the sheets via VBA code that way.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:15:01 -0800, mgroom
wrote:

I am new to macros, not to excel. I have a recap worksheet that gathers
information from various password protected worksheets. When I update the
recap worksheet, I have to enter each password every time before the data is
updated. How do I automatically have the passwords entered, either through a
macro or a formula?

I have protected worksheets for employee time sheets. Only the employee is
authorized to enter time information. I have a spreadsheet which pulls the
total hours from the individual employee worksheets that recaps the hours per
employee. In order to update the recap, since the formulas in each cell is
linking to the individual employee worksheets, I have to enter each password
manually. Is there a way to either amend the formula (per cell) to include
the passwords, or write a macro to do it upon opening of the recap worksheet?

I hope this helps.

Michelle

"Gord Dibben" wrote:

You don't need to unprotect worksheets to collect data from them.

What gathering are you doing that requires the unprotecting of the sheets?

Explain a little more the process you are performing.

If you do have to unprotect the multiple sheets for some reason like filtering
or sorting it would be best to have the same password for all sheets.

Easier to unprotect and reprotect the sheets via VBA code that way.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:15:01 -0800, mgroom
wrote:

I am new to macros, not to excel. I have a recap worksheet that gathers
information from various password protected worksheets. When I update the
recap worksheet, I have to enter each password every time before the data is
updated. How do I automatically have the passwords entered, either through a
macro or a formula?

In your Recap sheet you have a formula such as

=SUM(BobSheet!(A1:A10)

BobSheet does not have to be unprotected or even visible in order for the
formula to update.

What type of formula do you have in your Recap sheet to pull Bob's hours?

Give an example please.

BTW.....there is no way to enter a password via a formula, only manually or
through VBA

A thought just occurred to me..............by employee "worksheets" do you mean
sheets within a "workbook" or single workbooks for each employee?

Also, what is your interpretation of a "spreadsheet"?


Gord


On Tue, 4 Mar 2008 16:17:00 -0800, mgroom
wrote:

I have protected worksheets for employee time sheets. Only the employee is
authorized to enter time information. I have a spreadsheet which pulls the
total hours from the individual employee worksheets that recaps the hours per
employee. In order to update the recap, since the formulas in each cell is
linking to the individual employee worksheets, I have to enter each password
manually. Is there a way to either amend the formula (per cell) to include
the passwords, or write a macro to do it upon opening of the recap worksheet?

I hope this helps.

Michelle

"Gord Dibben" wrote:

You don't need to unprotect worksheets to collect data from them.

What gathering are you doing that requires the unprotecting of the sheets?

Explain a little more the process you are performing.

If you do have to unprotect the multiple sheets for some reason like filtering
or sorting it would be best to have the same password for all sheets.

Easier to unprotect and reprotect the sheets via VBA code that way.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:15:01 -0800, mgroom
wrote:

I am new to macros, not to excel. I have a recap worksheet that gathers
information from various password protected worksheets. When I update the
recap worksheet, I have to enter each password every time before the data is
updated. How do I automatically have the passwords entered, either through a
macro or a formula?

There are bi-weekly sheets within each employee's workbook. Within the
sheets are two weekly "timecards" reflecting in and outs, with a total field.
On the recap sheet (spreadsheet), the first column lists the employee names,
one per row. The second column and remaining columns are each week, starting
with the first week. The formula is [+BobSheet!D20]. The BobSheet is
password protected.

When I open the recap sheet, I cannot update the data (which changes daily)
without entering each password for each employee's workbook.

Thanks.


"Gord Dibben" wrote:

In your Recap sheet you have a formula such as

=SUM(BobSheet!(A1:A10)

BobSheet does not have to be unprotected or even visible in order for the
formula to update.

What type of formula do you have in your Recap sheet to pull Bob's hours?

Give an example please.

BTW.....there is no way to enter a password via a formula, only manually or
through VBA

A thought just occurred to me..............by employee "worksheets" do you mean
sheets within a "workbook" or single workbooks for each employee?

Also, what is your interpretation of a "spreadsheet"?


Gord


On Tue, 4 Mar 2008 16:17:00 -0800, mgroom
wrote:

I have protected worksheets for employee time sheets. Only the employee is
authorized to enter time information. I have a spreadsheet which pulls the
total hours from the individual employee worksheets that recaps the hours per
employee. In order to update the recap, since the formulas in each cell is
linking to the individual employee worksheets, I have to enter each password
manually. Is there a way to either amend the formula (per cell) to include
the passwords, or write a macro to do it upon opening of the recap worksheet?

I hope this helps.

Michelle

"Gord Dibben" wrote:

You don't need to unprotect worksheets to collect data from them.

What gathering are you doing that requires the unprotecting of the sheets?

Explain a little more the process you are performing.

If you do have to unprotect the multiple sheets for some reason like filtering
or sorting it would be best to have the same password for all sheets.

Easier to unprotect and reprotect the sheets via VBA code that way.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:15:01 -0800, mgroom
wrote:

I am new to macros, not to excel. I have a recap worksheet that gathers
information from various password protected worksheets. When I update the
recap worksheet, I have to enter each password every time before the data is
updated. How do I automatically have the passwords entered, either through a
macro or a formula?

What you are saying is you have to supply the password to open the workbook
before you can gather the data from the worksheets.

That's a whole different problem. The password protected sheets are not the
issue.

I can give you code to pull data from a closed workbook that is password
protected for modifying only.

Ron de bruin's site.

http://www.rondebruin.nl/copy7.htm

If the workbook has a password to open have a look at this google search result
for the WriteResPassword method.

http://tinyurl.com/369mon


Gord

On Tue, 4 Mar 2008 18:33:07 -0800, mgroom
wrote:

There are bi-weekly sheets within each employee's workbook. Within the
sheets are two weekly "timecards" reflecting in and outs, with a total field.
On the recap sheet (spreadsheet), the first column lists the employee names,
one per row. The second column and remaining columns are each week, starting
with the first week. The formula is [+BobSheet!D20]. The BobSheet is
password protected.

When I open the recap sheet, I cannot update the data (which changes daily)
without entering each password for each employee's workbook.

Thanks.


"Gord Dibben" wrote:

In your Recap sheet you have a formula such as

=SUM(BobSheet!(A1:A10)

BobSheet does not have to be unprotected or even visible in order for the
formula to update.

What type of formula do you have in your Recap sheet to pull Bob's hours?

Give an example please.

BTW.....there is no way to enter a password via a formula, only manually or
through VBA

A thought just occurred to me..............by employee "worksheets" do you mean
sheets within a "workbook" or single workbooks for each employee?

Also, what is your interpretation of a "spreadsheet"?


Gord


On Tue, 4 Mar 2008 16:17:00 -0800, mgroom
wrote:

I have protected worksheets for employee time sheets. Only the employee is
authorized to enter time information. I have a spreadsheet which pulls the
total hours from the individual employee worksheets that recaps the hours per
employee. In order to update the recap, since the formulas in each cell is
linking to the individual employee worksheets, I have to enter each password
manually. Is there a way to either amend the formula (per cell) to include
the passwords, or write a macro to do it upon opening of the recap worksheet?

I hope this helps.

Michelle

"Gord Dibben" wrote:

You don't need to unprotect worksheets to collect data from them.

What gathering are you doing that requires the unprotecting of the sheets?

Explain a little more the process you are performing.

If you do have to unprotect the multiple sheets for some reason like filtering
or sorting it would be best to have the same password for all sheets.

Easier to unprotect and reprotect the sheets via VBA code that way.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:15:01 -0800, mgroom
wrote:

I am new to macros, not to excel. I have a recap worksheet that gathers
information from various password protected worksheets. When I update the
recap worksheet, I have to enter each password every time before the data is
updated. How do I automatically have the passwords entered, either through a
macro or a formula?