Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheets.Unprotect not working | Excel Programming | |||
Unprotect not working | Excel Programming | |||
Protect/Unprotect Not Working | Excel Programming | |||
Protect/Unprotect w/ VBA not working as supposed... | Excel Programming | |||
Unprotect not working | Excel Programming |