how to disable cells if selecting a value using drop down

js1537

New Member
Joined
May 8, 2012
Messages
21
Hi,

I have two values (Yes and No) in a drop down in A1. If i select yes then only cells (B1 to G1) should be disbaled. i.e. no one can select cells (B1 to G1) or make non editable cells. If i select No then these cells (B1 to G1) should be editable or selectable.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi,

I have two values (Yes and No) in a drop down in A1. If i select yes then only cells (B1 to G1) should be disbaled. i.e. no one can select cells (B1 to G1) or make non editable cells. If i select No then these cells (B1 to G1) should be editable or selectable.
I suggest this code:
Code:
Dim lastC As Range   'This line MUST BE on top of the module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PC As String
If UCase(Range("A1").Value) = "YES" Then
    If Application.Intersect(Target, Range("B1:G1")) Is Nothing Then
        Set lastC = Target
        Exit Sub
    Else
        If lastC Is Nothing Then PC = "A1" Else PC = lastC.Address
        Application.EnableEvents = False
        Range(PC).Select
        Application.EnableEvents = True
    End If
End If
End Sub
Right click on the tab with the name of the sheet, select "View Code"; copy the above code and paste it into the empty frame at the right.
Then return to the Excel sheet ad test the behaviour.

Bye
 
Upvote 0
Thanks Anthony, This code is not working.
"not working" is not a valid answer...
How did you use the code, how did you test it, what happen when you chose Yes in A1 and then select B1, is there any additional macro in the module that hosts the Private Sub Worksheet_SelectionChange, which Excel version do you use? Is there any formula in A1?

Bye
 
Upvote 0

Forum statistics

Threads
1,226,724
Messages
6,192,682
Members
453,746
Latest member
Bruce at Pubdarts

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
Back
Top