Unprotect Sheet not working
 

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

Unprotect Sheet not working
 

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" wrote in message
...
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

Unprotect Sheet not working
 

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


"Mike Fogleman" wrote in message
m...
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" wrote in message
...
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

Unprotect Sheet not working
 

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

Excel Protect Sheet Error
 

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