using VBA on protected sheets, is it possible?

[mantrid]

Hello
I am having problems using the VB code below (which basically copies and
inserts a row) to work on a protected sheet, despite the protection being
set to allow insertion of rows. It works fine when sheet is unprotected. Is
there a way around this

With ActiveSheet
..Rows(rowno - 1).Copy
..Rows(rowno - 1).Insert (xlShiftDown)
..Paste Destination:=ActiveSheet.Rows(rowno)
End With

Thanks
Ian

 

[Norman Jones]

Hi Ian,

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 Autpen procedures, e.g.:

'=============>>
Private Sub Workbook_Open()
With Me.Worksheets("Sheet1")
.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.

 

[mantrid]

Thanks Norman
I did as you suggested. But it didnt work.
The code I use to copy and past a row is on a button's click event on a user
form. Its when this button is clicked I wish to override the protection so
the row can be inserted. The rest of the time I wish the sheets to be
protected.

Ian

 

[Gary Keramidas]

just surround your code with unprotect/protect code

With Worksheets("sheet1")
.Unprotect
'insert your row code here
.Protect
End With

 

[Norman Jones]

Hi Ian,

The code works for me.

The worksheet remains protected but may be
manipulated by VBA code.

To activate the protection either save, close and
re-open the file or run the Workbook_Open
procedure manually: select the procedure in the
ThisWorkbook module and hit the F5 key.

 

[mantrid]

ok
thats probably the reason. i didnt save first. will try it
thanks again