How to inactive cell

[lemukutan]

Hi,

How to inactive a cell for a certain value of another cell ?
example :
I have 2 cell with "data validation's list", let says:
A1 contain list of : New, Used, Take Over
A2 contain list of : Deposit, Credit Card, Other

How can i inactive cell A2 if the value chosen in A1 is only "Take Over"?

Sorry for my poor English,
Thanks

 

[HalfAce]

Hi lemukutan, welcome to the board.

How can i inactive cell A2 if the value chosen in A1 is only "Take Over"?

Here's one way you might try.
Right click the sheet tab, choose View Code and paste something like this into the sheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Range("A1").Value = "Take Over" Then
  MsgBox "The second dropdown is inactive due to the choice in the first."
  Range("A1").Select
End If
End Sub

Sorry for my poor English

Please... you're speaking more than one language! That's better than most can do.
(At least better than I can do anyway!)

Hope it helps.

 

[lemukutan]

Thanks a lot HalfAce, it works...

 

[Kris_inv2013]

Hi all,

This is brilliant. I wonder if someone could help me to configure this to my requirements...

I am working on something similar, I would like to gray out H4 (which is a cell with dropdwon list) based on the option selected in cell C4.
C is an other drop down list with 3 options.

I would like H4 to be active if Option1 is selected in C4. In case of Opt2 & Opt3 H4 should be grayed out as it becomes an irrelevant field.


Many thanks in advance.

Kris

Hi lemukutan, welcome to the board.
Here's one way you might try.
Right click the sheet tab, choose View Code and paste something like this into the sheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Range("A1").Value = "Take Over" Then
  MsgBox "The second dropdown is inactive due to the choice in the first."
  Range("A1").Select
End If
End Sub

Please... you're speaking more than one language! That's better than most can do.
(At least better than I can do anyway!)

Hope it helps.

 

[HalfAce]

OK, here's one way you might do it.
(Pretty much the same as the one above.)

I used the dropdown choices for C4 as being "First", "Second" & "Third".
You'll simply want to amend the code to look for whatever your real first choice is from your C4 dropdown instead of looking for the word "First".

The following goes into the sheet code module for your sheet of interest.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("H4")) Is Nothing Then Exit Sub

If Range("C4").Value = "First" Then 'replace "First" with your real first choice.
  MsgBox "The dropdown in H4 is inactive due to the choice made in cell C4."
  Range("A1").Select
End If

End Sub

Is that what you wanted to do?

 

[Kris_inv2013]

Almost, thanks a lot!
Actually I want the opposit, active F4:H4 if Opt1 and inactive F4:H4 if Opt2 & Opt3.

This seems to be working, is it okay if I just insert an elseif snippet? Is there a way to gray the text out in the inactive cells also?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("H4")) Is Nothing Then Exit Sub
If Range("C4").Value = "Vertical, Fan Bar" Then 'replace "Second" with your real first choice.
MsgBox "The dropdown in H4 is inactive due to the choice made in cell C4."
Range("A1").Select
ElseIf Range("C4").Value = "Vertical, Fan on Engine" Then 'replace "Third" with your real third choice.
MsgBox "The dropdown in H4 is inactive due to the choice made in cell C4."
Range("A1").Select
End If
End Sub


Many thanks,

Kris

 

[HalfAce]

Providing I understand your posted example, here's a way you should be able to do that.
Easier than an ElseIf statement on the C4 value, you can, instead, test if the value is NOT what you desire to keep the others enabled.
And then you can use the Worksheet_Change event for the font coloring. (Like so):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("F4:H4")) Is Nothing Then Exit Sub
If [C4].Value <> "Vertical, Fan Bar" Then
  MsgBox "The dropdowns in F4, G4 & H4 are inactive due to the choice made in cell C4."
  [A1].Select
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not [C4] = "" And [C4].Value <> "Vertical, Fan Bar" Then
  Range("F4:H4").Font.ColorIndex = 48
Else
  Range("F4:H4").Font.ColorIndex = xlAutomatic
End If
End Sub

(Did I interpret your post correctly?)

 

[Wonder01]

How do you run this code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Range("A1").Value = "Take Over" Then
MsgBox "The second dropdown is inactive due to the choice in the first."
Range("A1").Select
End If
End Sub

After pasting it in the VB worksheet?

 

[My Aswer Is This]

Wonder 01
What your script is doing:

What this script is saying is this:
If you have "Take Over" in Range("A1")

And you then select Range("A2") you will get a Message box to popup

Is that what you want it to do?
If not tell me what your wanting to do.