Solved: Make cells inactive

[sujittalukde]

In the attached file , if address for communication is selected as Office then the cells related to residence address shall become inactive & vice versa. How this can be done?

[Bob Phillips]

What does inactive mean?

[sujittalukde]

Means those cells will be visible but no entry can be made as can be seen on menubars when some icons are though seen but remain inactive. Here also the cells can be seen but no entry can be made

[Bob Phillips]

Okay, so here is what you do. First, select the cells that you can EVER put data in,k the DV cell and the related cells.

Go to Format>Cells>Protection, and uncheck the Locked box.

Protect the worksheet, Tools>Protect, no password.

Add this code

[vba]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Unprotect
If .Value = "Office" Then
Me.Range("B3").Locked = True
Me.Range("B4").Locked = True
Me.Range("B6").Locked = False
Me.Range("B7").Locked = False
Else
Me.Range("B3").Locked = False
Me.Range("B4").Locked = False
Me.Range("B6").Locked = True
Me.Range("B7").Locked = True
End If
Me.Protect
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Now you should be good to go.

[sujittalukde]

Tried the code but if "Office" is selcted in B1 then code is restricting to make entries in B3 & B4 and vice versa But when I have changed the conditions "true" to "False" and "False" to "True" all are working correctly.

[sujittalukde]

One thing when I try to put any data in restricted cells it is showing a message cell or chart you sre trying to change is protected and therefore read onlyetc Can this msg box be changed to display a message if the user tries to make entry in restricted cells as "Please make entries as per ypur selection"

[Bob Phillips]

That is the message put out by Excel, you can't change it.

All you could do is trap every selection and check the value in the DV box.

Don't understand the previous response.

[sujittalukde]

ok,clarification to the response not clear to you:
If the user selects "Office" in cell B1, the he should be allowed to make entry in cell B3 & B4.( if the user selects "Residence" in cell B1, the he should be allowed to make entry in cell B6 & B7)
If the code is pasted to the wb, if the user selects "Office" he cannot make entry in B3 & B4. So I have changed the "true" & "False" to "False" & "True" in your code. Then it worked correctly. i think this will clear the topic.

[Bob Phillips]

Here's the code for the alternative

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Address <> "$B$1" Then
If Me.Range("B1").Value = "Office" Then
If .Address <> "$B$3" And .Address <> "$B$4" Then
Me.Range("B3").Select
End If
ElseIf Me.Range("B1").Value = "Residence" Then
If .Address <> "$B$6" And .Address <> "$B$7" Then
Me.Range("B6").Select
End If
Else
Me.Range("B1").Select
End If
End If
End With
End Sub
[/vba]

[sujittalukde]

second code is really great.