Macro to prevent user from deletion specific rows contains words

[MKLAQ]

Hello
I search for macro to prevent user from deletion specific rows , but the user can edit them .
so shouldn't delete rows 54,55,56 at all , also in rows for column C contains words( PAID , DISCOUNT ,NET ) should not delete them totally .
when try deleting rows the range will be A54:H
thanks

 

[Killragtshirts]

Hi,

Have you seen this?

Worksheet.ProtectContents property (Excel)

Office VBA reference topic

learn.microsoft.com

 

[MKLAQ]

Hi
I don't want protection sheet. I want by macro in Worksheet_SelectionChange event .

 

[Joe4]

Protection really is the better way to go, because if the user elects to NOT enable VBA, no VBA will run, including Worksheet_SelectionChange events (so it is SUPER easy for them to bypass that code).

Don't be fooled by term "worksheet protection". It is NOT an "all or nothing" approach. You can actually protect just certain ranges, while allowing other ranges to be edited.
And a good rule of thumb is to not use VBA to recreate functionality which already exists out-of-the-box.

 

[MKLAQ]

Hi Joe
understood, but the matter is not as you think
there is many codes in the file so if the user doesn't enable macro then can't work on the file.
the aim of that just tell the user there are restricts when work on the file , not protect some sheets or ranges.
this thing I want show after enable macro . the user must enable macro .
thanks

 

[Joe4]

Hi Joe
understood, but the matter is not as you think
there is many codes in the file so if the user doesn't enable macro then can't work on the file.
the aim of that just tell the user there are restricts when work on the file , not protect some sheets or ranges.
this thing I want show after enable macro . the user must enable macro .
thanks

Why are you against using worksheet protection? If we understand why you DON'T want to use it, it may shed some light on what exactly you expect to happen.

I am not sure I understand how you want this to work.
I don't know that you can prevent them from editing a range with a "Worksheet_SelectionChange" procedure, which is triggered when a certain cell/range is selected.

How exactly do you envision this working?

 

[offthelip]

You can prevent the user from editing a range using VBA have alook at this thread:
Protect columns and cells via VBA
What I think is more difficult is to allow editing but prevent deletion!!

 

[MKLAQ]

Why are you against using worksheet protection?

so I have to unprotect when work the file sometime could occurs error in code because protection and will cause me headache and sometime I no know where is error, despite the codes are ok then I will be confused

I don't know that you can prevent them from editing a range with a "Worksheet_SelectionChange" procedure

I'm not saying to prevent them from editing a range , just prevent deletion specific rows
the user could edited as I said

but the user can edit them

just prevent deletion specific rows.

which is triggered when a certain cell/range is selected.

column A but should be specific rows , not any rows .

How exactly do you envision this working?

when select whole row and try delete manually ,then move next row , but doesn't include within my condition as in OP .

 

[MKLAQ]

@offthelip
Thanks but with Quick overview for the link my requirements are different because I want dong for specific rows.

 

[Joe4]

I might take a slightly different approach then. I may add automated code that disables the deletion of ALL rows, like this:

VBA Express : Excel - Prevent Row / Column Removal

Prevent Row / Column Removal Prevent a user from deleting rows or columns from any worksheet

www.vbaexpress.com

Then, I would probably create my own procedure that they have to run if they want to delete rows.
There, you can add your own rules to make sure only rows meeting certain criteria are deleted.

I am sure that there are other ways too, but that is one just one thought.