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

  • Thread starter StargateFanFromWork
  • Start date
S

StargateFanFromWork

Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub
Click to expand...

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!
 
D

Don Guillett

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
 
D

Dave Peterson

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.)
 
S

StargateFan

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
Click to expand...

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.
 
Top