Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protected Sheets | Excel Worksheet Functions | |||
protected sheets | Excel Worksheet Functions | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Protected sheets | Excel Worksheet Functions | |||
Protected sheets | Excel Programming |