Macro debug error on a protected workbook

[Geoffrey1231]

Hi there,
I'm trying to get a workbook to sort by a specific column upon opening. I was able to record a simple macro to get it to work. However, I also want the workbook protected. The macro works fine when the workbook is unprotected, but when I protect it, the macro gives me a debug error.

Thank you for the help,

Geoff

Windows 2000/ms office 2k.

 

[erik.van.geit]

Hi, Geoffrey1231,
try this

Private Sub Workbook_Open()
    ActiveWorkbook.Unprotect Password:="password"
    'your code
    ActiveWorkbook.Protect Password:="password"
End Sub

kind regards,
Erik

 

[Geoffrey1231]

This is what I have but I'm still getting the error;

Private Sub Workbook_Open()
ActiveWorkbook.Unprotect Password:="password"
' Sort Macro
' Macro recorded 11/11/2005 by
'

'
Selection.sort Key1:=Range("K5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

ActiveWorkbook.Protect Password:="password"
End Sub

 

[erik.van.geit]

what's "the" error ?
is your workbook password protected then you have to change "password"
else just delete the password-part
ActiveWorkbook.Unprotect Password:="EDIT THIS PART"
or
ActiveWorkbook.Unprotect

AND
this is dangerous coding
Selection.sort Key1:=Range("K5"), ...
if the selection doesn't contain K5, your code will bug
try something like
Range("K1:K50").sort Key1:=Range("K5"), ...

 

[Geoffrey1231]

Hi Erik,
I get this;
Run time error '1004"
Sort method of range class failed.

The workbook is password protected.

Just so I understand the original code.....was it unlocking the workbook with my password and then re-locking it?

If so, since I have to type my password in the vb code, what would keep someone from going into the vb code and stealing the password to the workbook?

Thank you for the help,

Geoff

 

[erik.van.geit]

Just so I understand the original code.....was it unlocking the workbook with my password and then re-locking it?

YES
you need to type your password in the VBcode
you will need to lock the project


BUT you don't have to unprotect the workbook; you should unprotect the SHEET of course !!!

    ActiveSheet.Unprotect "password"
    ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True

another option is to protect your sheets this way
Sheets(1).Protect Password:=newpw, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
then you won't need to Unprotect

getting further ?

 

[Geoffrey1231]

Hi Erik,
I was thinking about your second option (protecting the sheets differently). However, since I'm a real novice with these macros I was wondering how to do that? Where do I input that code? In a new macro?

Thanks again,

Geoff

 

[erik.van.geit]

Geoff,
you can protect all sheets using a loop

Sub ProtectAllSheets()
Dim sh As WorkSheet
    For Each sh in ThisWorkbook.Sheets
    sh.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True 
Next sh
End Sub

then this will sort your sheet without unprotect and protect

Sub Macro1()
    Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

best regards,
Erik