How to Lock and Unlock the cell of drop down list cell using formula

[sathishsusa]

Hi guys,

i want to Lock and unlock the cell by selecting the option of drop down list 1. lockcell and 2. unlock cell.

Eg:-

"A1" i have a drop down list to select items.
"B1" i have a drop down option selecting 1. lock cell 2.unlock cell ( after selecting list in "A1" i want to select the option 1. lock cell and i can't edit the cell "A1" unless selecting option 2. unlock cell to release cell to edit).

is there any formula to use to Lock and unlock the cells....

 

[portews]

The only thing I don't like about protecting the sheet is you have to hard code the password. Rather than locking the cells, how about not allowing the cursor to move to that cell, instead, moving the cursor to the B cell adjacent? Put this in the worksheet code page.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/980733-how-lock-unlock-cell-drop-down-list-cell-using-formula.html[/COLOR]
Dim x As Integer
[COLOR=#00ff00]'if the selected cell is in A column[/COLOR]
If Target.Column = 1 Then
[COLOR=#00ff00]    'if the adjacent cell in B says "Locked"[/COLOR]
    If Cells(Target.Row, 2) = "Locked" Then
[COLOR=#00ff00]        'disable events while you move the cursor[/COLOR]
        Application.EnableEvents = False
[COLOR=#00ff00]        'move the cursor to the column B cell[/COLOR]
        Cells(Target.Row, 2).Select
[COLOR=#00ff00]        'enable events again[/COLOR]
        Application.EnableEvents = True
[COLOR=#00ff00]        'throw a message box[/COLOR]
        x = MsgBox("Cell " & Target.Address & " is locked.  Unlock before editing.", 48, "Locked for Edit")
    End If
End If
End Sub

 

[sathishsusa]

hi portews,

thanks for reply ...

i tried ur code its not working i insert the code in sheet1 ... and i saved

after i selecting the drop down list in cell " A1" and " B1" i select lock its not locking the cell even i selecting " unlock cell " in "B1" so kindly pls help me again what i have to do ...

 

[portews]

What are you using for a dropdown in the B column, a validation, ActiveX control or a Form control? I was assuming a validation. You put the code in the code page for same sheet where the columns are, right? Put a breakpoint up at the top of the code, click in a locked A cell and try stepping through the code with the F8 key and see what it does.

 

[sathishsusa]

hi portews,

Thanks its working.... thanks a lot.

i need one more favour from u ... pls see the below picture to solve ......

[/IMG]

hi i need to solve this query can u pls help me....

Thanks in advance...

 

[portews]

What are the multipliers for each C cell? Are they always the same?

 

[sathishsusa]

What are the multipliers for each C cell? Are they always the same?

No it can be changed...

 

[sathishsusa]

It will be changed by some other units of formula mostly i need upper cell not to change when I reset button

 

[portews]

Based on what you've given me, and assuming that you have the formulas preset in the C column, the best idea I have would be to copy the results of the formula an paste it as a value.

Sub reset1()
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues
End Sub

 

[sathishsusa]

hi,
where i put the code in worksheet or in reset button because if i put in reset button the column "C" changing all value in "c" column.

Eg:- "E2" i am entering for "C5" after i finish i need to enter the value in "E2" for column "C6". i dont need to change above value of "C5".

bec ur code if again i enter the value in "E2" all the value in column " c" changing.

i think u got my point bill....