• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros to lock cells

Can you better define "lock"? Every cell has a property (format cells, protection) where you can change the status to "locked". What this property controls is if you protect the sheet then those cells are no longer editable. If this is the scneario you want, here's how:


Select entire sheet

Format cells, uncheck the "locked" option.

Select range D3:D50 and G3:G50.

Format cells, check the "locked" option.


Macro to protect sheet is straight forward:


Sub ProtectMe()

ActiveSheet.Protect

End Sub
 
I have formulas in this range, my problem is that if a user selects the range A to i & delete the data the formulas are also getting deleted I want to prevent this. I don't want to lock the sheet, if I lock the sheet when we press tab the cursor does not stand at the end of the cell it keeps going to the next cell (the below one). So i want to protect the cells using macros.
 
Not quite as secure, but you could try to undo any damaging actions I suppose...here's an event macro that might work for you. It needs to go in the sheet module, so right click on the sheet tab, view code, and paste this macro:


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D3:D50")) Is Nothing And _

Intersect(Target, Range("G3:G50")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Application.Undo

MsgBox "Don't touch my formulas!"

Application.EnableEvents = True

End Sub
 
Boss, one concern in the same excel i am using this macro

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("c3:c50")) Is Nothing Then

Exit Sub

Else

Cells(Target.Row, 4).ClearContents

Cells(Target.Row, 5).ClearContents

Cells(Target.Row, 7).ClearContents

Cells(Target.Row, 8).ClearContents

Cells(Target.Row, 10).ClearContents

Cells(Target.Row, 11).ClearContents

Cells(Target.Row, 12).ClearContents

Cells(Target.Row, 13).ClearContents

End If

End Sub


So I am not able to use the macro provided by you as it has the same macro name.

Please tell me a workaround for this, as I am not able to combine both the macros
 
It's not just the names that are the problem. Your original post stated that you have formulas in col D and G. However the macro you just posted clears out col D and G.


Which is it you want? Don't touch the formulas, or clean them?

I suppose if you really want them combined, you can do this:


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


If Not (Intersect(Target, Range("c3:c50")) Is Nothing) Then

Range(Cells(Target.Row, 4), Cells(Target.Row, 8)).ClearContents

Range(Cells(Target.Row, 10), Cells(Target.Row, 13)).ClearContents


ElseIf Intersect(Target, Range("D3:D50")) Is Nothing And _

Intersect(Target, Range("G3:G50")) Is Nothing Then

GoTo EscapeClause

Else

Application.Undo

MsgBox "Don't touch my formulas!"

End If


EscapeClause:

Application.EnableEvents = True

End Sub
 
Hi luke, Thanks, One more concerned


I have made the column changes in the macros given by you, what this macro does is that if you select column F & I try to edit or clear it then only it throws up the error, what I want is if a user select the all the column from B to M, then also it should show an error & if the user selects the columns which does not involve f & I, then the user can delete the contents.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


If Not (Intersect(Target, Range("c3:c50")) Is Nothing) Then

Range(Cells(Target.Row, 4), Cells(Target.Row, 5)).ClearContents

Range(Cells(Target.Row, 7), Cells(Target.Row, 8)).ClearContents

Range(Cells(Target.Row, 10), Cells(Target.Row, 13)).ClearContents


ElseIf Intersect(Target, Range("f3:f50")) Is Nothing And _

Intersect(Target, Range("i3:i50")) Is Nothing Then

GoTo EscapeClause

Else

Application.Undo

MsgBox "Don't touch my formulas!"

End If


EscapeClause:

Application.EnableEvents = True

End Sub
 
I'm not sure if you're asking a question or not...the macro you posted does exactly what you say.

1. If user changes col C, clear cells D:E,G:H,J:M

2. If user tries to change cells F or I, error out


Please clarify if the above is not what you want, or if you want additional critera.
 
The same sheet is used every week, Columns F & I contains formula.

The user wants to enter this weeks data, so he manually highlights the columns C to M & hits the delete button from the keyboard, all the data from column C to M get cleared, including the formulas from F & I.


What I want is if a user selects all the column from C to M manually, & hits the delete button, then also it should show an error. It should allow to delete the data manually only if the user selects the columns which does not include f & I.


The first macro given by you does what I want


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("f3:f50")) Is Nothing And _

Intersect(Target, Range("i3:i50")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Application.Undo

MsgBox "Don't touch my formulas!"

Application.EnableEvents = True

End Sub


But as I have already told you I have another macro with Private Sub Worksheet_Change(ByVal Target As Range, So I facing the problem.
 
There are two scenarios, the first one what you say & the second one is explained by me now.

Luke I really want to thank you, for showing so much of interest & really helping me out.
 
Your welcome, srinidhi. The 2nd macro covers BOTH of your scenarios (let's users clear cells quickly by changing col C but won't let user's delete the formulas) so you shouldn't need 2 macros anymore.


Is there still a question remaining?
 
You must log in or register to reply here.
Back
Top