Lock Unlock cells in VBA
 

hi - I've got a excel sheet which as a bunch of information that i dont want
the user to change and so tried using Protect sheet, however i have a toggle
code that is assigned to a object that hides and unhides a set of
rows.....this code doesnt seem to run when i have the sheet protected....

Sub SwitchView()
With Sheets("Info").Range("62:63").EntireRow
..Hidden = Not .Hidden
End With
End Sub

is there a way to still run this code when the sheet is protected?

Lock Unlock cells in VBA
 

Hi

..Hidden = False
..Hidden = True

Regards,
Per

"amit" skrev i meddelelsen
...
hi - I've got a excel sheet which as a bunch of information that i dont
want
the user to change and so tried using Protect sheet, however i have a
toggle
code that is assigned to a object that hides and unhides a set of
rows.....this code doesnt seem to run when i have the sheet protected....

Sub SwitchView()
With Sheets("Info").Range("62:63").EntireRow
.Hidden = Not .Hidden
End With
End Sub

is there a way to still run this code when the sheet is protected?

Lock Unlock cells in VBA
 

I think this will do what you want

Sub SwitchView()
With Sheets("Info")
.UnProtect '<--Unlocks Cells
With Range("62:63")
.EntireRow.Hidden = False '<--Shows Rows
End With
.Protect '<--Locks Cells
End With
End Sub


"amit" wrote:

hi - I've got a excel sheet which as a bunch of information that i dont want
the user to change and so tried using Protect sheet, however i have a toggle
code that is assigned to a object that hides and unhides a set of
rows.....this code doesnt seem to run when i have the sheet protected....

Sub SwitchView()
With Sheets("Info").Range("62:63").EntireRow
.Hidden = Not .Hidden
End With
End Sub

is there a way to still run this code when the sheet is protected?

Lock Unlock cells in VBA
 

Hi Armit,

You could unprotect the sheet, hide/unhide t
he rows and then reprotect it.

Something like:

'============
Sub SwitchView()
Dim SH As Worksheet
Const PWORD As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Info")
With SH
.Unprotect Password:=PWORD
With .Rows("62:63")
.Hidden = Not .Hidden
End With
.Protect Contents:=True, _
DrawingObjects:=True, _
Password:=PWORD
End With
End Sub
'<<============


Note howevewr that setting the Protect method's
UserInterfaceOnly parameter to true enables vba
manipulation of the protected sheet.

However, this setting is not persistent and needs to
be reset each time the workbook is opened.

Perhaps, therefore, you could set protection in the
Workbook_Open or Auto_Open procedures, e.g.:


'=============
Private Sub Workbook_Open()
With Me.Worksheets("Info")
.Protect Password:="drowssap", _
UserInterfaceOnly:=True
End With
End Sub
'<<=============

This is workbook event code and should be pasted
into the workbook's ThisWorkbook module *not* a
standard module or a sheet module:

Right-click the Excel icon on the worksheet (or the icon
to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.



---
Regards.
Norman


"amit" wrote in message
...
hi - I've got a excel sheet which as a bunch of information that i dont
want
the user to change and so tried using Protect sheet, however i have a
toggle
code that is assigned to a object that hides and unhides a set of
rows.....this code doesnt seem to run when i have the sheet protected....

Sub SwitchView()
With Sheets("Info").Range("62:63").EntireRow
.Hidden = Not .Hidden
End With
End Sub

is there a way to still run this code when the sheet is protected?