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

sathishsusa

New Member
Joined
Dec 7, 2016
Messages
30
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....
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

portews

Active Member
Joined
Sep 4, 2009
Messages
303
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.

Code:
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

New Member
Joined
Dec 7, 2016
Messages
30
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

Active Member
Joined
Sep 4, 2009
Messages
303
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

New Member
Joined
Dec 7, 2016
Messages
30

ADVERTISEMENT

hi portews,

Thanks its working.... thanks a lot.

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

2napcer.jpg
[/IMG]

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

Thanks in advance...
 

sathishsusa

New Member
Joined
Dec 7, 2016
Messages
30
It will be changed by some other units of formula mostly i need upper cell not to change when I reset button
 

portews

Active Member
Joined
Sep 4, 2009
Messages
303
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.

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

sathishsusa

New Member
Joined
Dec 7, 2016
Messages
30
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,127,210
Messages
5,623,401
Members
415,972
Latest member
SY1234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top