Tuesday, April 21, 2015

microsoft excel - Hide & Unhide macros won't work after locking columns & protecting sheet

I have two columns, E & G, that have formulas in them, so I locked those columns and protected the sheet they're in. I have two buttons on the sheet a Hide & Unhide button. After locking those columns & protecting the sheet, those macros assigned to those buttons do not work anymore. When I select the buttons, I receive a run-time error '1004', unable to set the Hidden property of the Range class.
The macro's code are as follows:



Sub Hide()
'

' Hide Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
For Each c In Range("C7:C446")
If IsEmpty(c.Value) Then
'If (c.Value) = "" Then
c.EntireRow.Hidden = True
End If
Next c


End Sub


Unhide macro:



Sub UH()
'
' UH Macro
'


'
Rows("6:447").Select
Selection.EntireRow.Hidden = False
End Sub


I think I have to add an unprotect command, which I've tried but it doesn't seem to work. I've tried:



Worksheets("JE").Unprotect Password:="ImportLCPA17", UserInterfaceOnly:=True



does anyone know of any adjustments I can make to have those hide & unhide macros, linked to the buttons, work while still having those columns locked?

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...