I am working on a workbook that I inherited and, with your help, I seem to have the code working correctly. I have been plagued with run-time errors relating to hide/show columns and rows, in conjunction with sheet protection.
The original code always works but mine (copied from the original) wouldn't when the sheet was protected, until board members suggested adding an unprotect/protect in the command.
Now I think my problem is again sheet protection. I am getting a Run-time error 1004 "Unprotect method of Worksheet class failed" message. I think the error comes in when the code re-protects the sheet. The protection I want, based on the original protected sheet, is Allow all users of this worksheet to:
Select locked cells
Select unlocked cells
Format columns
Format rows
Insert columns
Insert rows
Edit objects
Edit scenarios
I'm not sure which are needed, but these are the ones checked under the original sheet.
When I unprotect/reprotect manually after the error, only Select locked cells and Select unlocked cells are checked. For some reason, I don't think the code is reprotecting the cells correctly. I will post my code below.
For additional background, this workbook is being used in conjunction with a program for real property appraisals, where the user fills in fields in the program, the "builds" a workbook at which time the workbook populates all of the required fields using bookmarks. I'm not sure of how the program and excel interact, but it seems that a temp workbook is created, which is a shared workbook.
My code for one of the problem sheets is below. The other sheets use basically the same code, with different cell references.
Thank you (again) in advance.
Jim
Sub SalesHideCol()
Dim c As Range
ActiveSheet.Unprotect Password:="Hermes"
Application.ScreenUpdating = False
For Each c In Range("G1:K1") ' Adjust the range to suit
' Determine Criteria
c.EntireColumn.Hidden = c.Value = "H"
Next c
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="Hermes"
End Sub
The original code always works but mine (copied from the original) wouldn't when the sheet was protected, until board members suggested adding an unprotect/protect in the command.
Now I think my problem is again sheet protection. I am getting a Run-time error 1004 "Unprotect method of Worksheet class failed" message. I think the error comes in when the code re-protects the sheet. The protection I want, based on the original protected sheet, is Allow all users of this worksheet to:
Select locked cells
Select unlocked cells
Format columns
Format rows
Insert columns
Insert rows
Edit objects
Edit scenarios
I'm not sure which are needed, but these are the ones checked under the original sheet.
When I unprotect/reprotect manually after the error, only Select locked cells and Select unlocked cells are checked. For some reason, I don't think the code is reprotecting the cells correctly. I will post my code below.
For additional background, this workbook is being used in conjunction with a program for real property appraisals, where the user fills in fields in the program, the "builds" a workbook at which time the workbook populates all of the required fields using bookmarks. I'm not sure of how the program and excel interact, but it seems that a temp workbook is created, which is a shared workbook.
My code for one of the problem sheets is below. The other sheets use basically the same code, with different cell references.
Thank you (again) in advance.
Jim
Sub SalesHideCol()
Dim c As Range
ActiveSheet.Unprotect Password:="Hermes"
Application.ScreenUpdating = False
For Each c In Range("G1:K1") ' Adjust the range to suit
' Determine Criteria
c.EntireColumn.Hidden = c.Value = "H"
Next c
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="Hermes"
End Sub
