Drop-Down Menu with Multiple Conditions

[Anshu]

Part B)

I am slightly puzzled why you are not simply adding a drop down list here, as in Cell J19

I am not sure why clicking on a cell should be required to make something like a drop down list for that cell. Why not just make a drop down list for that cell?

I think I was unable to explain my points clearly. The reason for not adding a drop down list in the cell "R19" is-

As per my knowledge, a normal drop down list made by Data Validation tool, allows the user to enter the only values which are defined in the data validation list. That is, if the drop down list have only 2 menu, say "A" and "B", the user can not enter any other value in the cell other than "A' and "B".

Here, in my case, I've several occasions when I have to type many things in the cell "R19". I have to use the drop down list in the cell "R19" in only one case, and the case is "If cell J19 contain the value Single Parent Family". In all other cases, I need to enter data in the cell manually.

[DocAElstein]

Hi

....., I found

(1) When I select the cell "J19" and press the 'delete' key on my keyboard, the cell value is deleted, but the fade font "(Select)" does not appear in the cell "J19".
However, when I press the backspace key after selecting the cell "J19", "(Select)" appers.

(2) When I select any option from the drop down menu or enter manually any value in the cell "J19", it apperas in the cell in the same faded font, just like "Select". (Solved!)

(3) When I select an option in Cell "J19", say "Nuclear Family", the corresponding value in the cell "R19", for example "(Remark if any)", appears in faded font. That's nice.
But when I type something in the Cell "R19", it appears in the same faded font, just like "(Remarks if any)" .

Point (2)
I probably was not too sure exactly what you wanted., but your solution is OK.
Something of this form would also be OK

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$19" Then
        If Target.Value = "" Then
         Let Application.EnableEvents = False
         Let Target.Value = "(Select Here)"
         Let Application.EnableEvents = True
         Let Target.Font.Color = 10855845
        ElseIf Target.Value = "Nuclear Family" Or Target.Value = "Joint Family" Then
         Let Application.EnableEvents = False
         Let Range("R19").Value = "(Remark if any)"
         Let Application.EnableEvents = True
         Let Range("R19").Font.Color = 10855845
         Let Target.Font.Color = 6751362
        ElseIf Target.Value = "Single-Parent Family" Then
         Let Application.EnableEvents = False
         Let Range("R19").Value = "(Select Reason)"
         Let Application.EnableEvents = True
         Let Range("R19").Font.Color = 10855845
         Let Target.Font.Color = 6751362
        ElseIf Target.Value = "Uncategorised" Then
         Let Application.EnableEvents = False
         Let Range("R19").Value = "(Please Specify the Case)"
         Let Application.EnableEvents = True
         Let Range("R19").Font.Color = 10855845
         Let Target.Font.Color = 6751362
        End If
    Else
    ' Target is Not a cell to be acted on
    End If
End Sub

Point (1)
I was not sure myself what is going on there. It may be something to do with Merged cells.
( Merged cells often cause problems in VBA Many helpers at forums will not help anyone using Merged cells. You should try to avoid using merged cells as much as possible. Many professional programmers hate them! )

I did some experimenting:
I put a stop on the macro by clicking in the margin … https://imgur.com/bMVCshK BrownRoundStopInMargin.JPG
Then I do the …._
_ backspace key after selecting the cell, and in the Immediate window, type
? Target.Address
It gives me $J$19, as I expect
I then repeat the same experiment,
_using the Delete key
It gives me $J$19:$P$19
https://imgur.com/BB5KhGi Target_Address.JPG
So it would appear that in using the delete key, VBA thinks I was selecting all the merged cells

So a solution to this problem could be to replace
If Target.Address = "$J$19" Then
with
If Target.Address = "$J$19" Or Target.Address = "$J$19:$P$19" Then

But , this introduces another problem …
.Value applied to more than one cell will return an array() – a field of many values. For example, Range("$J$19:$P$19").Value will give us a row of 8 columns of data. This will cause problems in the rest of the macro. For example, an array cannot be equated to "" – So this following code line, for example, would error
If Range("$J$19:$P$19").Value = "" Then
One solution to this would be to replace Target in most of the coding with a range object variable, say RngTgt, then assign that to Range("$J$19") if Target is $J$19 or $J$19:$P$19

    Dim RngTgt As Range: Set RngTgt = Target
        If Target.Address = "$J$19:$P$19" Then Set RngTgt = Range("J19")

Point(3)
This is fairly easy. We just need an extra code section

    If Target.Address = "$R$19" Then
     Let Target.Font.ColorIndex = xlAutomatic
    Else
    End If

That could be simplified to

    If Target.Address = "$R$19" Then Let Target.Font.ColorIndex = xlAutomatic

So here is the next macro version from me: https://excelfox.com/forum/showthrea...ll=1#post14875

---

---

... The reason for not adding a drop down list in the cell "R19" is- .... As per my knowledge, a normal drop down list made by Data Validation tool, allows the user to enter the only values which are defined in the data validation list. That is, if the drop down list have only 2 menu, say "A" and "B", the user can not enter any other value in the cell other than "A' and "B".
Here, in my case, I've several occasions when I have to type many things in the cell "R19". I have to use the drop down list in the cell "R19" in only one case, and the case is "If cell J19 contain the value Single Parent Family". In all other cases, I need to enter data in the cell manually.

OK. That makes sense.
I do not know much about drop down lists and data Validation.
I have never used any drop down lists.
I am not too sure how to proceed further with this.
As I mentioned there are two possibilities, a UserForm solution or a simpler solution involving automating making a drop down list

.......
The coding to make a selectable list appear based on a cell selection may involve a use of UserForms. This is a somewhat advanced area of VBA. It is not one that I am too familiar with. I discussed this with you before : https://excelfox.com/forum/showthrea...ll=1#post14805
This might require a lot of work, which would take it out of the realms of a free help forum. You might be lucky and find someone willing to do this for you at one of the larger Excel Forums, but I think it is unlikely.

Alternatively, we may be able to get VBA to make a drop down list. I would start this option by running the macro recorder whilst making manually a drop down list for the cell R19. ....

I will post again if I have any more thoughts on this.


Alan ( Elston )