Block Delete/Insert row and column WITHOUT protecting sheet

[dmax17]

Hi,

I need to block Insert/Delete row commands WITHOUT protecting sheet. The reason is that I am adding rows in the sheet using VBA, but I do not want the user to have that ability beyond what the VBA affords them.

Anything I can do using VBA

Please help!

Thanks

 

[strategygamer]

If you're adding rows in VBA, can you unprotect the sheet, add the rows, then reprotect it?

ActiveSheet.Protect
Rows("10").Insert
ActiveSheet.Unprotect

 

[JoeMo]

Hi,

I need to block Insert/Delete row commands WITHOUT protecting sheet. The reason is that I am adding rows in the sheet using VBA, but I do not want the user to have that ability beyond what the VBA affords them.

Anything I can do using VBA

Please help!

Thanks

The worksheet.protect method includes an argument that allows VBA to work on the sheet as though it were not protected, but protects against changes through the user interface:

'your code
Sheets("mySheet").Protect password:="your pswd here", userinterfaceonly:= True
'your code

 

[dmax17]

Thanks Joe ! This works great. But now I have another problem, my users lose the ability to sort. I have unlocked those particular cells, but I still can't sort.
Is there a way I could ensure that users are able to sort on particular fields in the worksheet?

 

[JoeMo]

Thanks Joe ! This works great. But now I have another problem, my users lose the ability to sort. I have unlocked those particular cells, but I still can't sort.
Is there a way I could ensure that users are able to sort on particular fields in the worksheet?

You are welcome.

There's another argument for sorting:

'your code 
Sheets("mySheet").Protect password:="your pswd here", userinterfaceonly:= True, allowsorting:= True
'your code