Password not working on exported Workbook / Worksheet.

[Bill Case]

Hi;

I have built a little spreadsheet project form a club I am a member of. I
have one sheet of the Workbook password protected in order to make some
hidden columns in accessable to anyone other than executive members. The
password protection works fine on my computer, but when I transfer the file
to other club members the are not asked for a pass word wen they open the
hidden columns. Below is the VBA code I use to lock and protect those
columns.

Sub ExecProtect()
'
' ExecProtect Macro
' Macro recorded 08/21/2006 by William Case
'

'


Worksheets("Member_List").Protect _
Password:="bill", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterFaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=False, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

End Sub


Sub LockDef()

Sub BarLock()
'
' BarLock Macro
' Macro recorded 08/21/2006 by William Case
'
' Test and lock bar, address and fees columns
'

'
Worksheets("Member_List").Range("DataArea").Locked = False

Range(BarsToLock).Locked = True
Range(AddressToLock).Locked = True
Range(FeesToLock).Locked = True


End Sub



BarsToLock = "Bar1D,Bar2J,Bar3O,Bar4T,Bar5Y,Bar6AD,Bar7AI,Bar8AR,Bar9AZ"
AddressToLock = "Addresses"
FeesToLock = "Fees"

End Sub

Sub OpenAddresses()
'
' ExecProtect Macro
' Macro recorded 08/24/2006 by William Case
'

On Error GoTo OpenAddresses_Error

ExecUnprotect
Range(AddressToLock).Locked = False
UnHideAddresses
ExecProtect
'

On Error GoTo 0
Exit Sub

OpenAddresses_Error:
If Err.Description = "Method 'Range' of object '_Global' failed" Then
LockDef
Resume Next
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
OpenAddresses of Module LockandProtectMod"
End If
End Sub

There is several other procedures to cover a complex range of possibilites.
They all work for me as they should. But the password 'bill' is not asked
for when this project is installed on another Excel using machine and another
user unhides hidden columns.

What have I missed?

Regards Bill

 

[JLGWhiz]

Would they not have to run the macro again to reset the password when it is
set by code? I am not sure how that works.

 

[Bill Case]

Hi JLGWhiz;

Would they not have to run the macro again to reset the password when it is
set by code? I am not sure how that works.

I am not sure how the whole password thing works in Excel. What I have read
isn't more enlightening.

I use OfficeXP (i.e Excel 2003) my friend uses Excel 2000 as a stand alone.
Every thing on my machine works as it is supposed to. Yes, password was set
by the Worksheet.Protect properies. So, if it works for me, the same VBA
code should make it work for him. However, when he opens the workbook I sent
to him or uses the Unhide macro I wrote, he gets an error message generated
by On Error, and no password entry dialogue. The hidden columns Unhide
anyways.

Regards Bill

 

[Tom Ogilvy]

First, officeXP is Excel 2002, not Excel 2003. Office 2003 contains Excel
2003

xl2000 has a different protection model than Excel 2002 (office XP) and
Excel 2003.

Not sure how protection is managed in your workbook, but it is possible your
problem is related to this.

 

[Bill Case]

Hi Tom;

Exactly. At the risk of appearing dim-witted, I want to know how to write
the protection for Excel 2000 (and other versions if necessary)

I should be able to get the users version number through
'Application.Version' and if it's not 10.* (Excel 2002 --mine), I should be
able to write alternative protection code.

I have googled everywhere found lots of commentary and statements about
protection improvement in 2002 but no examples of how to deal with an earlier
version of Excel. Excel 2000 is probably all I need.

Regards Bill

 

[Tom Ogilvy]

Here is the command in Excel 2000

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


If you need the userInterfaceOnly agument, it has to be issued each time the
workbook is opened

ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, UserInterfaceOnly:=True