Disable insert row for workbook ONLY

[nickmax1]

I have the following code that disables the insert row option. The problem is that it works for ALL workbooks you have open, not just the one the code is in for. How do i change that so that it only works on the workbook the code is written into?

Please Login or Register  to view this content.

[freeofcost]

check if this helps

[nickmax1]

thats where i got the code from in the first place?!

[freeofcost]

why dont you go for sheet protection then with AllowInsertingColumns:=False, AllowInsertingRows:=False
You can have this in Private Sub Worksheet_Activate() so that whenever sheet comes to focus, inserting ability goes off

[nickmax1]

i cant go the sheet protection route because I do want the ability to insert colomns and rows but only via a userform i created. I dont want the users to be able to do it the right click way

[freeofcost]

Well I am not sure. however you may check if this works. having your enable/disable statements inside the For loop. This may work only for the worksheets in the active workbook.
I have not tried this btw

Please Login or Register  to view this content.

[romperstomper]

If you are using 2010 you can disable the command using XML in the CustomUI part of the workbook. It will then only apply to that workbook.

[freeofcost]

If you are using 2010 you can disable the command using XML in the CustomUI part of the workbook. It will then only apply to that workbook.

I would go with this option to avoid complications...
There are ways to do it in 2007 too with the an UI editor I guess

[nickmax1]

romperstomper - i am using 2010 - care to elaborate on how i do that?

[freeofcost]

Here is a wonderful resource for knowledge purpose
You may also have a look at the msdn articles. Its fun to play with the CustomUI

[nickmax1]

still dont see how disabling things on the ribbon will solve my problem??

[romperstomper]

You'll need the CustomUI editor - see Ron's site here: http://www.rondebruin.nl/win/section2.htm
There are lots of examples there - see the section on Context Menus (not Contextual tabs).

[nickmax1]

thanks romperstomper - unfortunately the spreadsheet is used at work, and maintained at work and IT has disabled the ability to install anything not authorised by company policy - so it needs to be VBA code.

[romperstomper]

In that case you need to use the same code from the Workbook_Deactivate and Workbook_Activate events to enable/disable the controls as required.

[nickmax1]

but wouldnt that only work if the workbook was closed? Users have my workbook open alongside another and the OTHER workbook is affected by the code in my workbook.

[romperstomper]

No - the Deactivate event will trigger when you switch to the other workbook.

[nickmax1]

Would that look like this?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[romperstomper]

Other way round since True actually enables the control:

Please Login or Register  to view this content.

and the events don't have parameters.

[nickmax1]

romperstomper....

thanks, unfortunately other workbooks are still effected.

EDIT : Figured out why....the code I have been using is totally rubbish it doesnt disable anything. I had another piece of code in there that kind of works which I have now put into workbook activate/deactivate which works (for delete rows only, i cant grey our insert rows annoyingly)

Please Login or Register  to view this content.

[romperstomper]

You have the wrong ID for Insert Row on the Row menu - you need:

Please Login or Register  to view this content.

[nickmax1]

awesome thats right!

my delete colomn code must be wrong too as it doesnt disable it

Please Login or Register  to view this content.

[romperstomper]

It's actually the same Id as the rows one - i.e. 3183