PDA

View Full Version : Sleeper: Add unprotected rows to protected sheet



wrussell
06-16-2005, 04:11 AM
I need code to be able to unprotect sheet add a row at a select selected place and then protect the sheet as before but leaving the new rows unprotected.

Would this best be done with a macro. I would also need to add a tool bar button or right click option (prefered) to do this.

Any help on this would be most appreciated. I am using Excel 2002 at the moment.

johnske
06-16-2005, 06:15 AM
Hi wrussell,

Welcome to VBAX

This goes into the ThisWorkbook module


Option Explicit

Private Sub Workbook_Open()
'//remove any previous right-click control
Run "RemoveFromMenu"
'//add new right-click control
With Application.CommandBars("Cell")
.Controls.Add(Type:=msoControlButton). _
Caption = "Insert Row"
'//assign procedure to this control
.Controls("Insert Row"). _
OnAction = "InsertRow"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'//remove the right-click control
Run "RemoveFromMenu"
End Sub

and this goes into a standard module[vba]Option Explicit


Sub InsertRow()
ActiveSheet.Unprotect
Selection.EntireRow.Insert
Rows(ActiveCell.Row).EntireRow.Locked = False
ActiveSheet.Protect
End Sub

Private Sub RemoveFromMenu()
On Error Resume Next '< error = no control
'//remove right-click control
With Application.CommandBars("Cell")
.Controls("Insert Row").Delete
End With
End Sub

Now close and re-open the workbook (to 'build' the right-click menu item) select a cell and right-click to insert a new row.

HTH,
John

Aaron Blood
06-16-2005, 08:11 AM
I need code to be able to unprotect sheet add a row at a select selected place and then protect the sheet as before but leaving the new rows unprotected.

Would this best be done with a macro. I would also need to add a tool bar button or right click option (prefered) to do this.

Any help on this would be most appreciated. I am using Excel 2002 at the moment.

Hmmmm... another protection toggler :doh:

You don't have to toggle the protection state of a worksheet just for the sake of running a macro. In fact, if the user does something to interrupt your macro, or the macro errs out before reprotecting, you leave your worksheet exposed.

Consider applying blanket protection to the workbook (every sheet) and using the UserInterfaceOnly property so your macros will run unhindered on the always protected sheets.

http://www.xl-logic.com/xl_files/vba/user_interface_only.zip

wrussell
06-17-2005, 01:53 AM
Many Thanks for your responses on this on. I have tried to amend the code to Add User Row rather than "Add Row" but the results do not seem to be consistent i.e. I have two menu options appear "Add Row" and "Add User Row" even if I am only adding "Add User Row" I still need to include:


.Controls("Insert Row").Delete
.Controls("Insert User Row").Delete


To generate the spreadsheet I am using Visual FoxPro via Automation So I need a little guidance on how I could add the macro programmatically using this method.



I can use:



oMacro = oExcelObject.Modules.Add()
oMacro.InsertFile(MacroFileName)
oMacroModule.Name = "InsertRow"
oExcel.Application.Run("InsertRow")



This would add the module code but would not add the Workbook code.


Any help with this would be most appreciated

johnske
06-17-2005, 02:17 AM
Hi wrussell,

If you're going to change the name of the control you have to run 'RemoveFromMenu' on its own BEFORE you change the name, otherwise you'll have the old control there sorta permanently.

Remove the 'Private' from the 'RemoveFromMenu' macro, run it on its' own (check the control's actually gone) then rename everything in the VBE window by selecting 'Insert Row', go to Find (binocular image), select Current Project, Replace, then type in the new name and select Replace All.

As for the second part of your question - sorry, I can't help you there, just wait around till someone who can help you reads the thread.

Regards,
John

MWE
06-17-2005, 01:14 PM
Hi wrussell,

Welcome to VBAX

This goes into the ThisWorkbook module


Option Explicit

Private Sub Workbook_Open()
'//remove any previous right-click control
Run "RemoveFromMenu"
'//add new right-click control
With Application.CommandBars("Cell")
.Controls.Add(Type:=msoControlButton). _
Caption = "Insert Row"
'//assign procedure to this control
.Controls("Insert Row"). _
OnAction = "InsertRow"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'//remove the right-click control
Run "RemoveFromMenu"
End Sub


and this goes into a standard module

Option Explicit

Sub InsertRow()
ActiveSheet.Unprotect
Selection.EntireRow.Insert
Rows(ActiveCell.Row).EntireRow.Locked = False
ActiveSheet.Protect
End Sub

Private Sub RemoveFromMenu()
On Error Resume Next '< error = no control
'//remove right-click control
With Application.CommandBars("Cell")
.Controls("Insert Row").Delete
End With
End Sub

Now close and re-open the workbook (to 'build' the right-click menu item) select a cell and right-click to insert a new row.

HTH,
John
John: why do you suggest that the "Remove" proc be in a standard module vs in the ThisWorkBook module.

johnske
06-17-2005, 01:44 PM
Hi Mark,

No real particular reason except that it needs to be run manually (i.e. as a normal macro) from time to time. E.G. as in the circumstances above where someone wants to change the name.

If it's in the ThisWorkbook module it shows on your list of macros as ThisWorkbook.RemoveFromMenu instead of just RemoveFromMenu.

John

wrussell
06-21-2005, 02:41 AM
Thanks for all your help.


I only need to programatically add the Code associated with the ThisWorkbook. Does any body know what object method is used to bring this code into the sheet.

This is the code that I want to add to the Microsoft Excel Objects:


Option Explicit

Private Sub Workbook_Open()
'//remove any previous right-click control
Run "RemoveFromMenu"
'//add new right-click control
With Application.CommandBars("Cell")
.Controls.Add(Type:=msoControlButton). _
Caption = "Insert Row"
'//assign procedure to this control
.Controls("Insert Row"). _
OnAction = "InsertRow"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'//remove the right-click control
Run "RemoveFromMenu"
End Sub

johnske
06-21-2005, 03:36 AM
Hi wrussell,

You have to select Project Explorer in the VBE window first, then double-click the "ThisWorkbook" module and paste the code in there. (Have a look at the attachment)

Regards,
John :)

wrussell
06-26-2005, 01:05 PM
I have now completed this task many thanks for all your help on this thread. I ended up using a tempate with macro already loaded.

johnske
06-26-2005, 02:17 PM
I have now completed this task many thanks for all your help on this thread. I ended up using a tempate with macro already loaded.

Not a prob, glad to be able to help (so you can mark this solved now?)

Regards,
John

mdmackillop
06-26-2005, 02:25 PM
Hi WRussell,
Question retitled relevant to subject.
Regards
MD

geekgirlau
06-26-2005, 06:31 PM
wrussell, I'm pretty sure version 2002 allows you to setup protection in such a way that it allows the user to insert rows. Provided the current row is not locked, the new row inserted will also be unlocked.

When you select Tools | Protection | Protect Sheet make sure Insert Rows is ticked - no macro required!