using VBA on protected sheets, is it possible?
 

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

using VBA on protected sheets, is it possible?
 

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 Auto_Open 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.


---
Regards,
Norman


"mantrid" wrote in message
...
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

using VBA on protected sheets, is it possible?
 

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


"Norman Jones" wrote in message
...
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 Auto_Open 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.


---
Regards,
Norman


"mantrid" wrote in message
...
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

using VBA on protected sheets, is it possible?
 

just surround your code with unprotect/protect code

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


--


Gary


"mantrid" wrote in message
...
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


"Norman Jones" wrote in message
...
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 Auto_Open 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.


---
Regards,
Norman


"mantrid" wrote in message
...
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

using VBA on protected sheets, is it possible?
 

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.


---
Regards,
Norman


"mantrid" wrote in message
...
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


"Norman Jones" wrote in message
...
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 Auto_Open 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.


---
Regards,
Norman


"mantrid" wrote in message
...
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

using VBA on protected sheets, is it possible?
 

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

"Norman Jones" wrote in message
...
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.


---
Regards,
Norman


"mantrid" wrote in message
...
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


"Norman Jones" wrote in message
...
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 Auto_Open 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.


---
Regards,
Norman


"mantrid" wrote in message
...
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