Unprotect Sheet not working

[Iain Bishop]

The following routine is executed when the Workbook_BeforeSave event fires:

Public Sub SaveWagesCheckboxValues()
Dim lngStaffNo As Long

On Error GoTo Handler

Workbooks("1Wages.xls").Worksheets("Weekly Worksheet").Unprotect
Password:="pjjs"
For lngStaffNo = 1 To 24
Workbooks("1Wages.xls").Worksheets("Weekly
Worksheet").Cells(lngStaffNo + 1, 110).Value =
Workbooks("1Wages.xls").Worksheets("Weekly
Worksheet").OLEObjects("chkHoliday" & lngStaffNo).Object.Value
Next
Workbooks("1Wages.xls").Worksheets("Weekly Worksheet").Protect
Password:="pjjs"

Exit Sub

Handler:
MsgBox "Error in Wages modWindowMaintenance.SaveWagesCheckboxValues: " &
Err.Number & " " & Err.Description, vbOKOnly, "ZR"
Exit Sub
End Sub


On the line "Workbooks("1Wages.xls").Worksheets("Weekly
Worksheet").Cells(lngStaffNo + 1, 110).Value = ..." I get the error 'The
cell or chart you are trying to change is protected and therefore
read-only.'
The password is correct, everything is qualified. Does anyone know why this
is failing. Is it something to do with the Workbook_BeforeSave event ?

Many Thanks,
Iain

 

[XLNut1958]

Hello Ian,

Instead of putting the password separately, try doing as outlined below

Workbooks("1Wages.xls").Worksheets("Weekly Worksheet").Unprotect Password:="pjjs"

Then do the same for protect (.Protect Password)

Best,
Steve

 

[Mike Fogleman]

Are you sure you have not double qualified your object with:
OLEObjects("chkHoliday" & lngStaffNo).Object.Value

have you tried:
OLEObjects("chkHoliday" & lngStaffNo).Value

Mike F

 

[Iain Bishop]

The " .Value " property does not exist for " OLEObjects("chkHoliday" &
lngStaffNo) "

The econd part of the line:
Workbooks("1Wages.xls").Worksheets("Weekly
Worksheet").OLEObjects("chkHoliday" & lngStaffNo).Object.Value
is definitely returning the correct value.

The error is raised on the first part of the line:
Workbooks("1Wages.xls").Worksheets("Weekly Worksheet").Cells(lngStaffNo + 1,
110).Value =

I have just added a command button which executes the routine when it is
clicked. The routine works fine when the command button is clicked. It is
just when it is called from the Workbook_BeforeSave event that it errors.
Interesting... maybe you can't unprotect a sheet within the
Workbook_BeforeSave event !!

 

[Guest]

This line
Password:="pjjs"
needs to be on the same line as the line containing the Unprotect statement
because it is an argument to the Unprotect method. However, I can't tell from
your post whether it exists that way in your original code or was just
affected by wordwrap from posting. If your original code has it on the same
line then try the following code. If it still returns an error then what type
of OLEObjects are you referring to?

Public Sub SaveWagesCheckboxValues()
Dim lngStaffNo As Long

On Error GoTo Handler
With Workbooks("1Wages.xls").Worksheets("Weekly Worksheet")
.Unprotect "pjjs"
For lngStaffNo = 1 To 24
.Cells(lngStaffNo + 1, 110).Value = _
.OLEObjects("chkHoliday" & lngStaffNo).Object.Value
Next
.Protect "pjjs"
End With
Exit Sub
Handler:
MsgBox "Error"
End Sub

Regards,
Greg

 

[PJ]

I'm having a similar issue & it appears to be because the data that I'm transferring into the unprotected sheet is arriving after the re-protect code executes.

My guess (& I emphasize "guess" since I haven't worked around this yet)... is that your loops are still in process while the re-protection takes place.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com