Hi! Urgent pls: Protecting sheets so macros/buttons still work?

Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word")
End Sub

I am having so much trouble getting the syntax for this right. The above is
what can be done to unprotect a sheet then protect so that a macro can
complete its function. Since there are many sheets would really, really
like to have only one macro for them all, otherwise it's going to be a major
headache. Then there's the issue of using the code in other workbooks.
Unprotect/protect code that works on any sheet in any book would be a really
big help (I'd also use it on the other macros, too, so that buttons work
across the board.)

Also, none of my workbooks ever have a password. I protect them, yes, but
without one.

How can I make the above code work, then, pls?? Here's a sample of what the
it might look like, though the unprotect/protect parts are giving me errors.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

Thank you so very much!

What was your selection?

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")
But you might like this better to sort without ever going there. Notice
where the periods are " . "

with worksheets("yours")
..unprotect
..range("yourrange").Sort Key1:=.Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
..protect
end with



--
Don Guillett
SalesAid Software

"StargateFanFromWork" wrote in message
...
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word")
End Sub

I am having so much trouble getting the syntax for this right. The above
is
what can be done to unprotect a sheet then protect so that a macro can
complete its function. Since there are many sheets would really, really
like to have only one macro for them all, otherwise it's going to be a
major
headache. Then there's the issue of using the code in other workbooks.
Unprotect/protect code that works on any sheet in any book would be a
really
big help (I'd also use it on the other macros, too, so that buttons work
across the board.)

Also, none of my workbooks ever have a password. I protect them, yes, but
without one.

How can I make the above code work, then, pls?? Here's a sample of what
the
it might look like, though the unprotect/protect parts are giving me
errors.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

Thank you so very much!

"Don Guillett" wrote in message
...
What was your selection?

I'm sorry, I don't understand the question. I'm a real newbie at all of
this, so pls bear with me.

The only issue I have is with the unprotect/protect portion of the code.
The sort coding itself was done recording the actual sort process. It works
fine, so I don't want to touch it. I have too many of these macros over
many workbooks, it would be too difficult to go back and start changing it
all.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

This is the basic type of structure I need. The sort code can stay as is
for each of the many sort macros there are. It's the unprotect/protect
syntax that needs modifying so that it works.

But you might like this better to sort without ever going there. Notice

Huh? Without ever going where? Sorry, wording is vague. Thanks.

where the periods are " . "

with worksheets("yours")
.unprotect
.range("yourrange").Sort Key1:=.Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.protect
end with

With worksheets("yours")
..unprotect

"yours"??? Do I key in "yours"?? I imagine not, eh? This probably means
to put a sheet name in???? sigh

The whole idea is to _not_ have sheet names. Pls, pls I can't stress that
enough. I have literally about 15 workbooks, or more, which have various
sheets in them, multiplied by having to have this unprotect/protect code put
in several of the macros for each sheet so that they will work while the
workbook is protected.

Can you imagine the headache and heartache if I start having to name all the
sheets? That means duplicating the macros x number of times on top of what
I already have to deal with so that the unprotect/protect code with the
names of the sheets is pertinent???!! God, I shudder to think. sigh If
a generic code can be used, as I've mentioned, then I can have the 4-7
macros per _workbook_ using the identical unprotect/protect code. Much,
much easier to handle for all the sheets and all the workbooks! That would
keep the workbooks down to 4-7 macros rather than god knows how many!! g
The alternative doesn't bear thinking of - no protection!! If I can't
figure out how to unprotect/protect in this way, then I'll have to leave
them all unprotected.

I leave this contract in 2 weeks time. I really would love to know that
I've done the best I can in providing them here with what they need yet in a
way that can't be screwed up easily.

So, I'm hoping I've explained properly this time. It's only what the
unprotect/protect script syntax is exactly so that I _don't_ key in a sheet
name and _don't_ key in a password.

I'd google for this, but I've not been successful in finding what the
variations in syntax might be yet that still work. I was hoping there would
be a script builder/command builder somewhere, but I've asked and asked and
no-one has let me know if there is something or not. I don't like pestering
the group so much for specific code, but what can one do??

Anyway, thank you so much; I appreciate all the help.

Signed: Getting a Tad Desperate at Work! g

--
Don Guillett
SalesAid Software

"StargateFanFromWork" wrote in message
...
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word")
End Sub

I am having so much trouble getting the syntax for this right. The
above
is
what can be done to unprotect a sheet then protect so that a macro can
complete its function. Since there are many sheets would really, really
like to have only one macro for them all, otherwise it's going to be a
major
headache. Then there's the issue of using the code in other workbooks.
Unprotect/protect code that works on any sheet in any book would be a
really
big help (I'd also use it on the other macros, too, so that buttons work
across the board.)

Also, none of my workbooks ever have a password. I protect them, yes,
but
without one.

How can I make the above code work, then, pls?? Here's a sample of what
the
it might look like, though the unprotect/protect parts are giving me
errors.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

Thank you so very much!

As written you did not have a worksheet named
worksheet("") is not a name
you did not have a range selected in the code

If you don't want help, don't ask for it.
Happy Trails

--
Don Guillett
SalesAid Software

"StargateFanFromWork" wrote in message
...
"Don Guillett" wrote in message
...
What was your selection?

I'm sorry, I don't understand the question. I'm a real newbie at all of
this, so pls bear with me.

The only issue I have is with the unprotect/protect portion of the code.
The sort coding itself was done recording the actual sort process. It
works
fine, so I don't want to touch it. I have too many of these macros over
many workbooks, it would be too difficult to go back and start changing it
all.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

This is the basic type of structure I need. The sort code can stay as is
for each of the many sort macros there are. It's the unprotect/protect
syntax that needs modifying so that it works.

But you might like this better to sort without ever going there. Notice

Huh? Without ever going where? Sorry, wording is vague. Thanks.

where the periods are " . "

with worksheets("yours")
.unprotect
.range("yourrange").Sort Key1:=.Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.protect
end with

With worksheets("yours")
.unprotect

"yours"??? Do I key in "yours"?? I imagine not, eh? This probably means
to put a sheet name in???? sigh

The whole idea is to _not_ have sheet names. Pls, pls I can't stress that
enough. I have literally about 15 workbooks, or more, which have various
sheets in them, multiplied by having to have this unprotect/protect code
put
in several of the macros for each sheet so that they will work while the
workbook is protected.

Can you imagine the headache and heartache if I start having to name all
the
sheets? That means duplicating the macros x number of times on top of
what
I already have to deal with so that the unprotect/protect code with the
names of the sheets is pertinent???!! God, I shudder to think. sigh
If
a generic code can be used, as I've mentioned, then I can have the 4-7
macros per _workbook_ using the identical unprotect/protect code. Much,
much easier to handle for all the sheets and all the workbooks! That
would
keep the workbooks down to 4-7 macros rather than god knows how many!! g
The alternative doesn't bear thinking of - no protection!! If I can't
figure out how to unprotect/protect in this way, then I'll have to leave
them all unprotected.

I leave this contract in 2 weeks time. I really would love to know that
I've done the best I can in providing them here with what they need yet in
a
way that can't be screwed up easily.

So, I'm hoping I've explained properly this time. It's only what the
unprotect/protect script syntax is exactly so that I _don't_ key in a
sheet
name and _don't_ key in a password.

I'd google for this, but I've not been successful in finding what the
variations in syntax might be yet that still work. I was hoping there
would
be a script builder/command builder somewhere, but I've asked and asked
and
no-one has let me know if there is something or not. I don't like
pestering
the group so much for specific code, but what can one do??

Anyway, thank you so much; I appreciate all the help.

Signed: Getting a Tad Desperate at Work! g

--
Don Guillett
SalesAid Software

"StargateFanFromWork" wrote in message
...
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word")
End Sub

I am having so much trouble getting the syntax for this right. The
above
is
what can be done to unprotect a sheet then protect so that a macro can
complete its function. Since there are many sheets would really,
really
like to have only one macro for them all, otherwise it's going to be a
major
headache. Then there's the issue of using the code in other
workbooks.
Unprotect/protect code that works on any sheet in any book would be a
really
big help (I'd also use it on the other macros, too, so that buttons
work
across the board.)

Also, none of my workbooks ever have a password. I protect them, yes,
but
without one.

How can I make the above code work, then, pls?? Here's a sample of
what
the
it might look like, though the unprotect/protect parts are giving me
errors.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

Thank you so very much!

"Don Guillett" wrote in message
...
As written you did not have a worksheet named
worksheet("") is not a name
you did not have a range selected in the code

That's what I was asking help about. I was given code that perforce had to
have a worksheet named. As I mentioned, I cannot put the name of the
worksheet. I need the syntax for a generic script that does _not_ call for
the worksheet name. I don't know what that would be or where to get it.

If you don't want help, don't ask for it.

Wow. I am begging for help, not even asking. I've written several times
trying to explain in different ways. I'm not an expert. That's why I have
come here to the experts trying to get help on this.

What else can I do?

I'll try one more time to explain my dilemma. I guess I'm hoping that if I
try to explain it using different words, that it might be clearer??

The code I was _given_, I did not come up with this code, calls for placing
this type of syntax at beginning of the macro:

ThisWorkbook.Worksheets("").Unprotect ("")

I believe that the name of the worksheet goes between the first set of
quotation marks and that the second set of quotation marks stands for the
password.

I _cannot_ starting naming the sheets and I _don't_ put passwords on my
workbooks. I protect them without.

What I have practically been begging for is code that would allow one to do
the same but _without_ using worksheet names and _without_ putting a
password code. I don't know how else to describe the problem.

At the beginning of the code, something like the above is supposed to be
typed in. And after, the same code is put in but using ".protect" rather
than ".unprotect".

Pls, if there is a better ng for this, I will gladly go for help there. I
don't know what else to do ... I have called my Help people here and there
are no VB guys there. I have absolutely no-one to turn to. Pls tell me
where would be the more appropriate place, if not here, and I will gladly go
for help.

This is the last that is standing in the way of having these workbooks
protected, yet that will allow the macros to work.

Thank you.

Happy Trails

--
Don Guillett
SalesAid Software

"StargateFanFromWork" wrote in message
...
"Don Guillett" wrote in message
...
What was your selection?

I'm sorry, I don't understand the question. I'm a real newbie at all of
this, so pls bear with me.

The only issue I have is with the unprotect/protect portion of the code.
The sort coding itself was done recording the actual sort process. It
works
fine, so I don't want to touch it. I have too many of these macros over
many workbooks, it would be too difficult to go back and start changing
it
all.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

This is the basic type of structure I need. The sort code can stay as
is
for each of the many sort macros there are. It's the unprotect/protect
syntax that needs modifying so that it works.

But you might like this better to sort without ever going there.
Notice

Huh? Without ever going where? Sorry, wording is vague. Thanks.

where the periods are " . "

with worksheets("yours")
.unprotect
.range("yourrange").Sort Key1:=.Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.protect
end with

With worksheets("yours")
.unprotect

"yours"??? Do I key in "yours"?? I imagine not, eh? This probably
means
to put a sheet name in???? sigh

The whole idea is to _not_ have sheet names. Pls, pls I can't stress
that
enough. I have literally about 15 workbooks, or more, which have
various
sheets in them, multiplied by having to have this unprotect/protect code
put
in several of the macros for each sheet so that they will work while the
workbook is protected.

Can you imagine the headache and heartache if I start having to name all
the
sheets? That means duplicating the macros x number of times on top of
what
I already have to deal with so that the unprotect/protect code with the
names of the sheets is pertinent???!! God, I shudder to think. sigh
If
a generic code can be used, as I've mentioned, then I can have the 4-7
macros per _workbook_ using the identical unprotect/protect code. Much,
much easier to handle for all the sheets and all the workbooks! That
would
keep the workbooks down to 4-7 macros rather than god knows how many!!
g
The alternative doesn't bear thinking of - no protection!! If I can't
figure out how to unprotect/protect in this way, then I'll have to leave
them all unprotected.

I leave this contract in 2 weeks time. I really would love to know that
I've done the best I can in providing them here with what they need yet
in
a
way that can't be screwed up easily.

So, I'm hoping I've explained properly this time. It's only what the
unprotect/protect script syntax is exactly so that I _don't_ key in a
sheet
name and _don't_ key in a password.

I'd google for this, but I've not been successful in finding what the
variations in syntax might be yet that still work. I was hoping there
would
be a script builder/command builder somewhere, but I've asked and asked
and
no-one has let me know if there is something or not. I don't like
pestering
the group so much for specific code, but what can one do??

Anyway, thank you so much; I appreciate all the help.

Signed: Getting a Tad Desperate at Work! g

--
Don Guillett
SalesAid Software

"StargateFanFromWork" wrote in message
...
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word")
End Sub

I am having so much trouble getting the syntax for this right. The
above
is
what can be done to unprotect a sheet then protect so that a macro
can
complete its function. Since there are many sheets would really,
really
like to have only one macro for them all, otherwise it's going to be
a
major
headache. Then there's the issue of using the code in other
workbooks.
Unprotect/protect code that works on any sheet in any book would be
a
really
big help (I'd also use it on the other macros, too, so that buttons
work
across the board.)

Also, none of my workbooks ever have a password. I protect them,
yes,
but
without one.

How can I make the above code work, then, pls?? Here's a sample of
what
the
it might look like, though the unprotect/protect parts are giving me
errors.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

Thank you so very much!

You could write the code for the activesheet:

With ActiveSheet
.Unprotect
.Range("a1").CurrentRegion.Sort Key1:=.Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Protect
End With



or you could run it against all the worksheets in the activeworkbook.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect
.Range("a1").CurrentRegion.Sort Key1:=.Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Protect
End With
next wks

But if the range to sort varies between sheets, then this may not work.

You can see what .range("a1").currentregion will be by:
selecting A1
hitting ctrl-*

(or edit|goto|special|and click current region.)





StargateFanFromWork wrote:

Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word")
End Sub

I am having so much trouble getting the syntax for this right. The above is
what can be done to unprotect a sheet then protect so that a macro can
complete its function. Since there are many sheets would really, really
like to have only one macro for them all, otherwise it's going to be a major
headache. Then there's the issue of using the code in other workbooks.
Unprotect/protect code that works on any sheet in any book would be a really
big help (I'd also use it on the other macros, too, so that buttons work
across the board.)

Also, none of my workbooks ever have a password. I protect them, yes, but
without one.

How can I make the above code work, then, pls?? Here's a sample of what the
it might look like, though the unprotect/protect parts are giving me errors.

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

Thank you so very much!

--

Dave Peterson

On Fri, 02 Jul 2004 21:20:13 -0500, Dave Peterson
wrote:

You could write the code for the activesheet:

YES!!

With ActiveSheet
.Unprotect
.Range("a1").CurrentRegion.Sort Key1:=.Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Protect
End With

Dave, you have come to my rescue yet again!! THANK YOU, THANK YOU,
THANK YOU!! I Just knew there had to be an easier way and this seems
to be it!

This did the trick and in my test workbook, I had a grand total of TWO
macros for 10 sheets as they all used the same buttons, it's only the
data that changed. 20 buttons but two macros ... awesome!!

I was becoming a bit desperate (like no-one noticed, I'm sure g.
It's my half Latin side, I guess) as I only have 2 weeks left in this
contract. I will fix all the workbooks on Monday, a very daunting
job. But this easy syntax will make it a breeze, comparatively, from
waht I was facing before!! sigh It'll still take me an hour or
two, if not more, but it's a global change to apply to all affected
macros so it'll be just a question of copy/pasting at the appropriate
spots. Simple if time-consuming.

I'll be leaving behind working files that are protected. They
couldn't ask for more than that.

I'm also set for life! I can't tell you how many times I've been
asked to handle data that requires a database yet they insist I use
Excel. With the skills I've learned in last 2 or 3 weeks, I can
deliver the best compromise - Excel with macros/buttons to give a lot
of database-like functionality!!

Thank you once again from the bottom of my heart! Much, much
appreciated.

On Fri, 2 Jul 2004 14:47:04 -0400, "StargateFanFromWork"
wrote:

"Don Guillett" wrote in message
...

[snip]

ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")

I posted the fix for this but I accidentally deleted a line. I can't
find that post, so I'm re-sending again to the thread.

The code that will allow macros to work in protect sheets is this:



Sub MacroName()
'
'
With ActiveSheet
.Unprotect
(code goes here)
.Protect
End With
End Sub



Elegantly simple!

Much thanks to Dave Peterson for this code!