How to Lock /Unlock Celles based on value in a drop down list
Requirement:
The user is trying to write VBA code to lock or unlock certain cells based on the value in another cell.
The user has a drop down list in cell A2 (containing: cash, cheque, debit, visa, m/c,amex)
the above cash, cheque, etc are headings in cells B1:G1.
If cell A2 equals cash then B2 is unlocked and C2:G2 will be locked.
This formula will repeat for all payment types shown above.
Solution:
One method:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("A2") = "Cash" Then
Range("B2").Locked = False
Range("C2:G2").Locked = True
Else
Range("B2").Locked = True
Range("C2:G2").Locked = False
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by amiK.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to sum up columns in each row and highlight until that value |
| How to use RANK function ignoring only zero values |
| How to data trim and clean cell values with VBA code |
| How to create and auto run macro if value on cell A1 is less than value on B2 |
| How to use a macro to copy rows from multiple worksheets based on a cell value greater than zero |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
