PDA

View Full Version : Solved: command for Sheets("All")??



sunilmulay
10-24-2008, 08:22 AM
Hi there
Quick question. I want to create a macro to protect all my sheets in the workbook with my set password... Currently, I'm using a command like: Sheets("1TR").Unprotect Password:=pWord, etc for each sheet. Is there a command such as Sheets(all) or something I can use????
Thanks
S

Paul_Hossler
10-24-2008, 08:31 AM
Dup, and I don't know how to delete it

Paul_Hossler
10-24-2008, 08:31 AM
Quick way



Dim ws as Worksheet

For Each ws in Activeworkbook.Worksheets
ws.Protect ........
etc.
Next

Paul

sunilmulay
10-24-2008, 07:43 PM
So, I tried the following and something's not quite right...
Sorry, I'm a beginner......
Sub ProtectAllSheets()
Dim ws As Worksheets
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=pWord
Next
End Sub

georgiboy
10-24-2008, 11:05 PM
You should have defined ws as Worksheet and not Worksheets because your going to look at each Worksheet in ActiveWorkbook.Worksheets, not each Worksheets in ActiveWorkbook.Worksheets.
Secondly you only need to put the password you want to use in speach marks after the ws.Protect, like this ws.Protect "pWord".

Hope this helps you understand

sunilmulay
10-25-2008, 02:11 AM
thanks for your mini lesson....

mdmackillop
10-25-2008, 03:10 AM
Secondly you only need to put the password you want to use in speach marks after the ws.Protect, like this ws.Protect "pWord".

Not quite right
pWord is a variable, "pWord" is a string. If you were using Option Explicit (as you always should) pWord will error.

georgiboy
10-25-2008, 03:16 AM
Being self taught does bring me problems :igiveup:, keep an eye on me especialy when i am trying to give advice.:blush Thanks

mdmackillop
10-25-2008, 03:30 AM
A lot of us are self taught, and it's an ongoing process!
HTH.

GTO
10-25-2008, 06:12 AM
Hi All, and of course most importantly Suni, as it's your question.

As we are all here at the town's square (forum) to learn and or share information, we don't spend too awful much time being social. While in yesteryear, we probably would have spent more, typing is just much slower; thus - the loss of actual socialization. Anyways - wanted to say Howdy to Georgi and Malcom. I have and continue to learn a lot from ya'll. (You can barely tell I've may have been out drinking a bit, eh?)

Anyways and at hand:


Malcom and Georgi (Paul too), my respect of course, however I am very confident that my poor memory has this much 'storage'. Suni's project does indeed have pWord dim'd and assigned.

Suni:

If you're still checking this thread and not sure, I believe you're original question (so much for a "quick" question) was, to wit and akin to: "Is there a better way than?

Sheets("1TR").Unprotect Password:= pWord
Sheets("Whatever").Unprotect Password:= pWord (etc, etc)"
==========================================

Yes.

Follow Paul's advice at post #3

You simply err'd in dimensioning WorkSheets instead of Worksheet.

@Malcom:

"We" (West side of pond) occassionally use "extra" vowels to make-up for other heritage(s) (where WAY too many consonants are used (Norwegian etc)).

You guys (lads) have a great weekend!

(Hope you're laffin')

Mark

georgiboy
10-25-2008, 06:27 AM
Hi Mark, i now see where i went wrong, i did not realise this was part of a bigger project where the pWord was in fact dim'd, now Malcolms advise to me makes more sence "pWord" being a string... Anyway nice to be here with you all i am learning in leaps and bounds, and i hope to be here for a long time.

GTO
10-25-2008, 06:38 AM
Hi Mark, ...Anyway nice to be here with you all i am learning in leaps and bounds, and i hope to be here for a long time.

Me too brother, both counts :beerchug: .

(@ previous comment of several beers, couldn't resist the painfully obvious 'beerchug'...)