macros

[mgroom]

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?

 

[Gord Dibben]

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

 

[mgroom]

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]

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

 

[mgroom]

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]

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

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.

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