How to lock specific cells using VBA after dropdown selection.

[subhan1029]

Hi All,
After Dropdown selection in C8,
I want to Lock specific cells from D8:J8
Can Anyone pls help me to write a VGA Code........
Thank you in advance......

 

[mumps]

Which specific cells in D8:J8 do you want to lock and what is the criteria for each cell?

 

[subhan1029]

If C8="Rectangular" then (D1,E1,i1,J1) should be lock
if C8="Circular" then (D1 to G1) should be lock
if C8= "ISA" or "ISMC" or "ISMB" then(E1,G8 to J8) Should lock
if C8="Fastener" then (E8 to J8) should lock
if c8="Flange" then (F8 to J8) Should be lock
if C8="Pipes" then(G8 to J8) Should be lock

*please provide the codes so that it automatically go's on next row.

Thank you so much for your reply
Hope you will help.

 

[mumps]

Start by unlocking all the cells in your sheet. The macro will protect the sheet with a password. This is necessary in order to lock the cells. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    ActiveSheet.Unprotect Password:="xxxx"
    ActiveSheet.Cells.Locked = False
    Select Case Target.Value
        Case "Rectangular"
            Range("D1,E1,I1,J1").Locked = True
        Case "Circular"
            Range("D1:G1").Locked = True
        Case "ISA", "ISMC", "ISMB"
            Range("E1").Locked = True
            Range("G" & Target.Row).Resize(, 4).Locked = True
        Case "Fastener"
            Range("E" & Target.Row).Resize(, 6).Locked = True
        Case "Flange"
            Range("F" & Target.Row).Resize(, 5).Locked = True
        Case "Pipes"
            Range("G" & Target.Row).Resize(, 4).Locked = True
    End Select
    ActiveSheet.Protect Password:="xxxx"
    ActiveSheet.EnableSelection = xlUnlockedCells
    Application.ScreenUpdating = False
End Sub

 

[subhan1029]

Hi....
Above code is not working for me
please find attached image for reference
kindly suggest what to do

Master BOQ - Copy - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L		N	O
1
2
3	ESTIMATION SHEET
4
5
6
7	Sr. No.	Description	C/S	Size	Sch / Type	L (mm)	W (mm)	Thk/Ht (mm)	OD (mm)	ID (mm)	MOC (mm)	Qty (Nos)		Weight (Kg)	Amount
8	1	Example	Rectangular			1234	1234	1234	1234	1234
9	2	Example	Circular			1234	1234	1234	1234	1234
10	3	Example	ISA	65 x 65 x 8		1234	1234	1234	1234	1234				0.00
11	4	Example	Pipes	_80NB	20	1234	1234	1234	1234	1234				0.00
12	5	Example	Fastner			1234	1234	1234	1234	1234
13	6	Example	Circular			1234	1234	1234	1234	1234
14	7	Example	Fastner	M12 x 65		1234	1234	1234	####	1234				0.00
15	8	Example	Flange	_.40NB	WNRF									0.00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:O17	Cell	contains a blank value	text	NO

 

[subhan1029]

Hi....
Above code is not working for me
please find attached image for reference
kindly suggest what to do

Master BOQ - Copy - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L		N	O
1
2
3	ESTIMATION SHEET
4
5
6
7	Sr. No.	Description	C/S	Size	Sch / Type	L (mm)	W (mm)	Thk/Ht (mm)	OD (mm)	ID (mm)	MOC (mm)	Qty (Nos)		Weight (Kg)	Amount
8	1	Example	Rectangular			1234	1234	1234	1234	1234
9	2	Example	Circular			1234	1234	1234	1234	1234
10	3	Example	ISA	65 x 65 x 8		1234	1234	1234	1234	1234				0.00
11	4	Example	Pipes	_80NB	20	1234	1234	1234	1234	1234				0.00
12	5	Example	Fastner			1234	1234	1234	1234	1234
13	6	Example	Circular			1234	1234	1234	1234	1234
14	7	Example	Fastner	M12 x 65		1234	1234	1234	####	1234				0.00
15	8	Example	Flange	_.40NB	WNRF									0.00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:O17	Cell	contains a blank value	text	NO

It is perfectly working for "Flanges" only

 

[mumps]

I tested the code on the data you posted and it works as designed, locking the cells that are hard coded in the macro. The code refers to some cells in row 1 that are blank. Is that what you want? Did you unprotect all the cells in your sheet before trying the macro? Explain in detail how it is not working for you referring to specific cells, rows and columns using a few examples from your data.