Diable Insert Rows.

[roadstain]

How can I set up a workbook so that if a user is editing a cell within a named range then the user cannot insert a row using a right click after selecting the row or going up to 'Insert' then 'Row' on the menu bar. Reason I ask is I have a named range in which is filled with formulas and so forth that have to be on every row of the document within this named range. Thus I set up a CNTR-I based macro that inserts a row and places the needed formulas in the right cell locations within the named range. Problem is some users to not use the CNTR-I command and end up inserting a row the manual way. Is there a way around this as I have been working on this for a while. Any advice you have would be appreciated.

 

[kevinclark]

One quick way maybe ?
Via Format Cells Protection unloack any cells that you would like the user to be able to edit (You could unloack all cells for example) and then Protect the worksheet. This should prevent them from inserting new rows and or columns.
Then add something like the following to your CNTRL-I code:


'Unlock Sheet for Row Insert
Sheets("Sheet1&quot.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False

'Existing Code to insert Row etc ....

'Then Loack Sheet
Sheets("Sheet1&quot.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[roadstain]

I thought about that but some of the cells within the named range are for users to enter data - some are a formula based on what they entered thus locking down the cells by protection might take some work. I have found a quick way to disable the right click popup menu (ended up being one of the examples in the MSDN Library) that includes the 'Insert' function but I have yet to determine how to disable the Command Bar Menu option.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True

End Sub

This will in end disable the right click and just not allow the user to see the popup menu. I am trying to modify this now to only work if the active cell or row is within the named cell range.

 

[kevinclark]

Actually you can select the entire worksheet in one shot and format the selected cells as unlocked. Even after you protect the worksheet these unlocked cells can be edited by the users. Again they would not be able to do is insert and or delete rows and columns. Should accomplish what you need quite easily once you add the two lines of code to unprotect the worksheet and then re-protect the worksheet?

 

[aMember]

I used the following to prevent users from deleting rows from a spreadsheets through the "normal" methods thus requiring them to use my own "delete" button.
I do not have the ability right now to look up InsertID for you. But, what you can do is set up a test function to loop
through all command bar controls until it find an Insert. (that is how I found the IDs for delete). I would disable the controls on the worksheet activate and enable them on workbook/worksheet deactivate.

Application.CommandBars.FindControl(ID:=InsertID).Enabled = False
Application.CommandBars.FindControl(ID:=InsertID).Enabled = False
Application.CommandBars.FindControl(ID:=InsertID).Enabled = False
Application.CommandBars.FindControl(ID:=InsertID).Enabled = False

Change False to True to restore the controls to normal usage. This will also prevent the user from inserting individual cells.

 

[Loomah]

Beware
I'd been messing with something like this and I lost the shortcut (right click) insert item from the menu. Didn't know how to find it again. Stuffed!!!

(Fixed it but would love to know what it is, ID wise as I fixed with a hammer)

Just incase your interested I've posted the code but it does come with the above warning! It is intended to disable if you are in your named range and enable if outside.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ctl As CommandBarControl
Dim myControls As CommandBarControls

If Not Intersect(Target, Range("edge&quot) Is Nothing Then
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, ID:=296)
For Each ctl In myControls
ctl.Enabled = 0
Next ctl
Else
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, ID:=296)
For Each ctl In myControls
ctl.Enabled = 1
Next ctl
End If
End Sub


;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[roadstain]

Nice - how did you fix this once you did it?

I am trying to look up the User ID's now to try and isolate this.

 

[Loomah]

Hi gritch
Like I said - with a hammer, a BIG hammer!!

I enabled ALL command bars/controls. Using the enabling section of the above code, omit the ID:=??? param. This is not a problem for me but.....

What I don't know is why it disabled ok but wouldn't enable everything properly. aMember or Kevin, or anyone any ideas???

That said, despite the work, I would probably go for the protection method suggetsted. If you have a sheet with all the cells that require data entry filled with somethng (not a formula) something like

range("A1&quot.specialcells(xlend).select 'should be xlcelltypeconstants or something but i can't remember!

should select them all. Then just unlock. Just a thought!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[aMember]

I didn't have any problems enabling/disable my delete function. Like I said, to find the IDs set up a loop and searched for any combination of "delete" I could find. That is, I searched for "d&elete" (which I believe is the name of delete when you right click), "delete" (from the pull down menu), etc. (Excuse me, I may have this backwards or off a bit. I don't have Excel accessible right now. PC problems..still...)

Still a "hammer" approach, but it was easy to do, and covered them all. Personally, I don't like the protection approach because I've come across situations where the code executes but fails to unprotect. (A bug in '97...if there is a control button with TakeFocusOnClick=true).

 

[Loomah]

ok
if anyone's still reading this i've lost the plot.
now this code isn't disabling the insert on shortcut menu. dunno how it ever did as 296 is '&Rows'

how can i find the ids for shortcut menus?

If a man says something and there are no women there to hear him, is he still wrong?

 

[kevinclark]

Might want to check this site out

http://www.erlandsendata.no/english/downloads/commandbars.htm

and look at downloading the CommandBar Tools