Drop-Down Menu with Multiple Conditions

2 Attachment(s)

Hello!

I've a sheet with only 2 two cell but with multiple conditions.

These are the conditions:
1. If the cell "J19" is empty, it show "(Select)" in fade font.
2. Cell "J19" has 4 Drop-Down Menu. When the first menu "Nuclear Family", or the 2nd menu "Joint Family" is selected, Cell "R19" show "(Remarks if any)" in fade font.
3. If the 3rd menu "Single-Parent Family" is selected, Cell "R19" show "(Select Reason)" in fade font.
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
5. On selecting an item, it apper in normal color and format
6. On selecting "Enter Reason Manually" from this drop-down, the cell "R19" becomes empty, so that the reason can be entered manually.
7. If the 4th menu "Uncategorised" is selected, Cell "R19" show "(Please Specify the Case)" in fade font.

Attachment 3379

All these conditions are well explained in the excel file attached below. Please have a look to understand clearly my points.

Thanks for looking my post!!

Hi
_... I would put your requirement into two broad areas,
_A) One I can help with,
the other
_B) I cannot, at least not yet…

Part B) ( 4. 5. 6. ) is really a completely separate issue to Part A) ( 1. 2. 3. 7. )

_Part A) I am considering 1. 2. 3. And 7. here
1. If the cell "J19" is empty, it show "(Select)" in fade font.
2. Cell "J19" has 4 Drop-Down Menu. When the first menu "Nuclear Family", or the 2nd menu "Joint Family" is selected, Cell "R19" show "(Remarks if any)" in fade font.
3. If the 3rd menu "Single-Parent Family" is selected, Cell "R19" show "(Select Reason)" in fade font.
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
5. On selecting an item, it apper in normal color and format
6. On selecting "Enter Reason Manually" from this drop-down, the cell "R19" becomes empty, so that the reason can be entered manually.
7. If the 4th menu "Uncategorised" is selected, Cell "R19" show "(Please Specify the Case)" in fade font

And I wonder if the table should look like this, which is slightly different to what you gave…

_____ Workbook: Book1.xlsx ( Using Excel 2007 32 bit )

_Row\^Col AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB

16 (Select Here)

16 Nuclear Family (Remark if any)

17 Joint Family (Remark if any)

18 Single-Parent Family (Select Reason)

19 Expired

20 Divorced

21 Break-Up

22 Abandonment

23 Enter Reason Manually

24 Uncategorised (Please Specify the Case)

Worksheet: Sheet1

I will move onto the next post, as I may need more space than the current post limit… _

_ continued from last post

Part A)

This is fairly similar to what we did here: https://excelfox.com/forum/showthrea...-in-excel-cell

My start point, as previously, would be to get some of the syntax for the coding, based on some investigation. This is because I don’t know, and will probably never want to learn, the almost infinite number of formatting possibilities and syntaxes
( By the way, I understand nothing about drop down boxes…. )
A lot of your requirements involve Event coding, so I am going once again into the worksheet object code module.
( Right click on the worksheet tab and select View Code https://imgur.com/klH9ehK )
( One of the main reasons why Microsoft have given us access rights to the object code module of worksheets is because there we find the event macros. They are all already there, and available to us, but we do not always see them until we access them , ( via the drop down lists there in the code module
First Select Worksheet : https://imgur.com/2UqSmkZ
Then choose a macro : https://imgur.com/n3kwQqR ).

From the VBEditor, if I hit the key combination, Ctrl+g , I can get the Immediate window, which I can drag around freely. I can even drag it around anywhere on my screen outside the VB Editor window
( https://imgur.com/MVp4geJ )

My next move is to type in the Immediate window a few things to help gat some syntaxes. First I type a ? , after which I can type typical VBA code lines.
I am looking at the formats in your list table, ( https://excelfox.com/forum/showthrea...ll=1#post14863 , https://excelfox.com/forum/showthrea...ll=1#post14864 )
Here are a few results, ( I got some the command ideas from my previous post: https://excelfox.com/forum/showthrea...ll=1#post14812 )
https://imgur.com/IJKPqSR
? Range("AM15").font.ThemeColor
7
? Range("AM15").font.TintAndShade
0
? Range("AM15").font.Color
10855845
? Range("AM15").font.Colorindex
48
? Range("AM16").font.tintandshade
0
? Range("AM16").font.Bold
Falsch
? Range("AM16").font.Color
6751362
? Range("AM16").font.Colorindex
13
? Range("AM16").font.Bold
Falsch
? Range("AT19").Font.Tintandshade
0
? Range("AT19").Font.Color
0
? Range("AT19").Font.colorindex
-4105
? Range("AT19").Font.Bold
Falsch

I think that gives me some of the formatting syntax that I need ,at least for the first half of the problem.
I probably don’t need all that information.

The coding is then very similar to what we did previously ( https://excelfox.com/forum/showthrea...ll=1#post14814 )
Possibly the use of ElseIf below maybe new to you.
( Some use of the formatting done by the coding may be unnecessary, at least at this stage. … )

Here is the coding for Part A) https://excelfox.com/forum/showthrea...ll=1#post14868

Part B)
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
5. On selecting an item, it appear in normal color and format
6. On selecting "Enter Reason Manually" from this drop-down, the cell "R19" becomes empty, so that the reason can be entered manually

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 need some clarity as to your reasons to do this, or possibly I have not understood exactly what you want.
You are asking for approximately 2 things in PartB):
B(i)) A selectable list to appear on cell selection. That is basically what a drop down list is
B(ii)) ( Your 6. ) This is similar to Part A)

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. But I am not sure what the advantages of this would be over simply having initially the drop down list there.
I did just record a macro whilst selecting the arrow on a drop down list. Unfortunately , no coding is produced by this: Not all actions taken manually produce coding by the macro recorder. So this does not help us.

I wonder if in PartB) , what you are asking for is something similar to what you asked for here: https://excelfox.com/forum/showthrea...-List-in-Excel ? – You requirement in Part B) , seems to be an attempt again to get a solution for a tree like Horizontal Hierarchical List structure…

I am not personally sure what is the best way for you to proceed with requirement Part B)
The only way I Know to do it would be a complicated coding involving UserForms. It is too advanced for you to be involved with, and it would be a lot of work for somebody to do for you…
Other possibly ways to do this were given by Logit : https://excelfox.com/forum/showthrea...ll=1#post14807
You also said you had ideas for how to do it. https://excelfox.com/forum/showthrea...ll=1#post14806

I think at the end of the day, you wont get an easy answer. Either you or someone else is going to have to take some time to do some work., unless you can find a finished solution that has already been done by someone.

Part B) ( 4. 5. 6. ) is really a completely separate issue to Part A) ( 1. 2. 3. 7. )

Alan

Thank for the Macro and explaining everything clearly and systematically!

Quote:

Here is the coding for Part A) https://excelfox.com/forum/showthrea...ll=1#post14868

While working with this Macro, 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), here, is solved by me by making a little bit changes in the provided macro. It is here...

Code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$J$19" Then If Target.Value = "" Then ' case a cell was emptied Let Application.EnableEvents = False Let Target.Value = "(Select Here)" Let Range("R19").Value = "" Let Application.EnableEvents = True With Target.Font .Color = -3817475 End With Else ' case a text was entered With Target.Font .Color = -12316781 End With End If Else ' Target is Not a cell to be acted on End If If Target.Address = "$J$19" Then If 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 With Range("R19").Font .Color = 10855845 '.ColorIndex = 48 End With ElseIf Target.Value = "Single-Parent Family" Then Let Application.EnableEvents = False Let Range("R19").Value = "(Select Reason)" Let Application.EnableEvents = True With Range("R19").Font .Color = 10855845 '.ColorIndex = 48 End With ElseIf Target.Value = "Uncategorised" Then Let Application.EnableEvents = False Let Range("R19").Value = "(Please Specify the Case)" Let Application.EnableEvents = True With Range("R19").Font .Color = 10855845 '.ColorIndex = 48 End With End If Else ' Target is Not a cell to be acted on End If End Sub

But I'm unable to solve the Point (3) issue. And point (1) is beyond my area of 'little" knowledge!

Quote:

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.

3 Attachment(s)

Hi

Quote:

Originally Posted by Anshu

....., 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

Code:

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 Attachment 3390
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 Attachment 3391
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

Code:

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

Code:

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

That could be simplified to

Code:

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

Quote:

Originally Posted by Anshu

... 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

Quote:

Originally Posted by DocAElstein

.......
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 ) :)

1 Attachment(s)

Finally SOLVED!!

Thank you so much Alan Sir for all the help and guidance!!

Quote:

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

This macro works just wow!

I've made some changes in the macro to fulfil the "Part B" case (Point 4,5,6)

Quote:

Part B)
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
5. On selecting an item, it appear in normal color and format
6. On selecting "Enter Reason Manually" from this drop-down, the cell "R19" becomes empty, so that the reason can be entered manually

This is the Macro which finally satisfies my all 7 Point needs. (red color indicates the area I've changed)

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ' https://excelfox.com/forum/showthread.php/2624-Drop-Down-Menu-with-Multiple-Conditions?p=14873&viewfull=1#post14873 If Target.Address = "$J$19" Or Target.Address = "$J$19:$P$19" Then ' we need "$J$19:$P$19" to make macro work on Delete probably because of merged cells Dim RngTgt As Range: Set RngTgt = Target If Target.Address = "$J$19:$P$19" Then Set RngTgt = Range("J19") If RngTgt.Value = "" Then Let Application.EnableEvents = False Let RngTgt.Value = "(Select Here)" Let Range("R19").Value = "" Let Application.EnableEvents = True Let RngTgt.Font.Color = 10855845 Range("R19:Z19").Select With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With Range("J19").Select ElseIf RngTgt.Value = "Nuclear Family" Or RngTgt.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 RngTgt.Font.Color = 6751362 Range("R19:Z19").Select With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With ElseIf RngTgt.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 RngTgt.Font.Color = 6751362 Range("R19:Z19").Select With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With End If Else ' Target is Not a cell to be acted on End If If Target.Address = "$R$19" Then Let Target.Font.ColorIndex = xlAutomatic If Target.Address = "$J$19" Then If Target.Value = "Single-Parent Family" Then Let Application.EnableEvents = False Let Range("R19").Value = "Select Reason..." Let Application.EnableEvents = True With Range("R19").Font .Color = -10477568 .TintAndShade = 0 End With Range("R19").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Expired,Divorced,Break-Up,Abandonment,Enter Reason Manually" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Error!" .InputMessage = "" .ErrorMessage = "To enter the reason manually, please select the option 'Enter Reason Manually'" .ShowInput = True .ShowError = True End With End If Else ' Target is Not a cell to be acted on End If If Target.Address = "$R$19" Then If Target.Value = "Enter Reason Manually" Then With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With Selection.ClearContents With Target.Font .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 End With Range("R19:Z19").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Target.Font.Size = 11.5 End If End If End Sub Sub Oops() Let Application.EnableEvents = True End Sub

For making these changes, I've recorded the macro by Macro recorder, and then organized them in the above code.

This macro works pretty well without any issue.

Thanks!!

1 Attachment(s)

Hi

Quote:

Originally Posted by Anshu

.....For making these changes, I've recorded the macro by Macro recorder, and then organized them in the above code.
However, I've some doubts regarding 'changes in macro made by me'.
I think, making a just 'working' macro is different from making a 'smooth and fluent' macro. It seems that a smooth macro puts less loads on system, and do the job efficiently....
So what about this final Macro?
Does it need any changes, or it's just ok??

OK, this is very good news that you have got to grips with the macro recorder.

The macro recorder is both
_ a good start point for a beginner
and also
_ it is still used by senior professional programmes, since it is much more efficient to run a macro recording to get some syntaxes rather than trying to learn the almost infinite syntax variations that VBA has.

You are correct that coding produced by a macro recorder is rarely fine and fluent, and some intelligent changing is usually beneficial.

Bear in mind that
_ I don’t have any experience at all with coding drop down list, so I can only give you some general tips on refining coding given by the macro recorder….A fully refined macro involving drop down lists/ validation I am unqualified/ unable to give
and
_ Also I am still not 100% clear on what is going on.. You say that one of your requirement s is:
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
But that does not happen. If I click on cell "R19" , then nothing happens. – It is not possible for anything to happen when cell R19 is clicked on, because… So far in this Thread we have been looking just at the event code of Worksheet_Change
This only reacts to a change. It does not react to a select ( clicking on a cell )

What I perceive as what appears to happen with you macro regarding cell R19 .. is the following:
The drop down validation list in cell R19 is produced when the value “Single-Parent Family” is selected in cell J19
If that drop down list is present in cell R19 , and “Enter Reason Manually” is selected from that drop down list in R19, then the drop down validation list in cell R19 is removed.
So any refinement on your coding that I do will reflect that perception

So …. , Some general ideas on changing a macro recorder produced coding .,
Two main points, the third point is not so important
_(i) The macro recorder does not have any idea what you are wanting to do. It tries to record exactly what you do.
_(ii) We have eyes. We must see and select cells in order to do anything. VBA does not have eyes. It does not need to see or select anything
_ ( _(iii) With End With )

The consequences are
_(i) Unnecessary stuff
Often much more is recorded then we need. For example, lots of formatting and lots of options (arguments := ) are included which we can simply remove as it serves no purpose for our requirements
_(ii) Activate, Select
It is rarely required to Activate or Select anything. We as humans do need to do this, but VBA does not.
As simple example:
Something like this:

Code:

Range("R19:Z19").Select With Selection

can almost always be replaced by

Code:

With Range("R19:Z19")

_(iii) With End With ( http://excelmatters.com/2017/02/28/whos-with-me/ )
The macro recorder produces this a lot. Possibly this is because it produces a lot of extra unnecessary stuff, ( as noted in _(i) )
With End With is helpful to keep things tidy if lots of things are done with something. Some programmers also use multiple With End With. This can be confusing to a beginner. I personally would avoid using it too much. You would have possibly noticed that previously I replaced something like

Code:

With Range("R19").Font .Color = 10855845 '.ColorIndex = 48 End With

with

Code:

Let Range("R19").Font.Color = 10855845

This is partially personal choice of mine.

So the changes I would do are due to 2 things
The issues discussed in _(i) _(ii) _(iii)
and
re writing the macro to follow what I see as the logic:
The drop down validation list in cell R19 is produced when the value “Single-Parent Family” is selected in cell J19
If that drop down list is present in cell R19 , and “Enter Reason Manually” is selected from that drop down list in R19, then the drop down validation list in cell R19 is removed.
I have done two versions of the next macro, here: https://excelfox.com/forum/showthrea...ll=1#post14880
The two macros are exactly the same except one macro is where I have 'commented out some things, and the other macro has those 'commented things removed

Alan

Thank you for refining the code and enhancing my understanding about Macro Recorder!
The code works absolutely fine.

The only issue I find is after having a drop down menu in cell R19, if I select any other value in cell J19 other than Single Parent Family, the validation list still exist in the cell R19, which prevents to enter anything new in the cell R19.

This issue is solved by just putting a line in the end of every "elseif line". This is highlighted in the code below..

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ' https://excelfox.com/forum/showthread.php/2624-Drop-Down-Menu-with-Multiple-Conditions?p=14873&viewfull=1#post14873 If Target.Address = "$J$19" Or Target.Address = "$J$19:$P$19" Then ' we need "$J$19:$P$19" to make macro work on Delete probably because of merged cells Dim RngTgt As Range: Set RngTgt = Target If Target.Address = "$J$19:$P$19" Then Set RngTgt = Range("J19") If RngTgt.Value = "" Then Let Application.EnableEvents = False Let RngTgt.Value = "(Select Here)" Let Range("R19").Value = "" Let Application.EnableEvents = True Let RngTgt.Font.Color = 10855845 Range("R19").Validation.Delete Range("R19").ClearComments ElseIf RngTgt.Value = "Nuclear Family" Or RngTgt.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 RngTgt.Font.Color = 6751362 Range("R19").Validation.Delete Range("R19").Select Range("R19").ClearComments ElseIf RngTgt.Value = "Single-Parent Family" Then Let Application.EnableEvents = False Let Range("R19").Value = "(Select Reason for Single-Parent)" Let Application.EnableEvents = True Let Range("R19").Font.Color = 10855845 Let RngTgt.Font.Color = 6751362 With Range("R19").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Expired,Divorced,Break-Up,Abandonment,Enter Reason Manually" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Error!" .InputMessage = "" .ErrorMessage = "To enter the reason manually, please select the option 'Enter Reason Manually'" .ShowInput = True .ShowError = True End With Range("R19").Select Range("R19").AddComment Range("R19").Comment.Visible = False Range("R19").Comment.Text Text:="Reason for Single-Parent" ElseIf RngTgt.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 RngTgt.Font.Color = 6751362 Range("R19").Validation.Delete Range("R19").Select Range("R19").ClearComments End If ' end of all values of J19 to result in actions Else ' Target is not cell J19 ( or J19:P19 ) End If If Target.Address = "$R$19" Then Let Target.Font.ColorIndex = xlAutomatic If Target.Value = "Enter Reason Manually" Then Target.Validation.Delete Target.Value = "" Else End If Else ' Target is not R19 End If End Sub

I'm surprisingly happy to see some changes in the macro, provided by you, which I really wanted but didn't know how to do that.
For example, this long code

Code:

Range("R19:Z19").Select With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With

is simply written in one line, as

Code:

Range("R19").Validation.Delete

__________________________________________________ __________________________

Quote:

4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
But that does not happen. If I click...

This line shows only the partial picture of the whole story. Hence, I think, it would be the reason for the misunderstanding.

The real story is here, which you have already solved nicely...

Quote:

The drop down validation list in cell R19 is produced when the value “Single-Parent Family” is selected in cell J19
If that drop down list is present in cell R19 , and “Enter Reason Manually” is selected from that drop down list in R19, then the drop down validation list in cell R19 is removed.

The final line is just one...
Thanks a lot!!

Hi,

A couple of comments about your last modifications…

I am not sure what the point is about the code lines that you have added like
Range("R19").Select
Range("R19").ClearComments
???

.ClearContents / ClearComments
.ClearComments removes any comments attached to cells ( https://support.microsoft.com/en-us/...2-0b2b5c7bf6f8 )

Possibly you were thinking of wanting to do
.ClearContents
But, as far as I can tell, there is never any need to do that. It appears that .Validation.Delete somehow already clears away any contents in the cell

.Select
You rarely need to do this. Possibly you have a reason that I don’t know about?

Note also, If you do want to .ClearContents, then you must be careful.
Two problems that I see
_1) Merged cells may cause you problems. You may need to replace
Range("R19").ClearContents
With
Range("R19:Z19").ClearContents
_2 If contents are cleared from a cell, then that is a worksheet_change. That worksheet change will start the macro starting again. In some cases it might even cause an infinite loop of re starting the macro which will cause problems and / or errors.

Before the macro tries to change anything, you must temporarily turn off macros set off by events.

So you would need to do this
replace
Range("R19:Z19").ClearContents
with
Let Application.EnableEvents = False ' Temporarily disable macros of this event kind
Range("R19:Z19").ClearContents
Let Application.EnableEvents = True ' Turn back on macros of this event kind

Quote:

Originally Posted by Anshu

...., this long code

Code:

Range("R19:Z19").Select With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With

is simply written in one line, as

Code:

Range("R19").Validation.Delete

I took a guess there that lots of the code lines were not needed. I am not 100% sure because I don’t have any experience with Drop down validation stuff. Often the way forward in such cases is to remove some code lines one by one, checking if it effects anything.

Alan

Hi!
Here I added some more event macro in the same sheet. "Case 1", "Case 3" and "Case 4" are added. "Case 2" is the same Macro discussed above.

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ' Case 1 If Target.Address = "$I$20" Or Target.Address = "$U$20" Or Target.Address = "$Z$20" Then If Target.Value = "" Then ' case a cell was emptied Let Application.EnableEvents = False Let Target.Value = ChrW(&H270F) Let Application.EnableEvents = True Let Target.Font.Color = -3817475 Target.Font.Size = 9 Else ' case a text was entered Let Target.Font.Color = -12316781 Target.Font.Size = 12 End If Else ' Target is Not a cell to be acted on End If ' Case 2 If Target.Address = "$J$19" Or Target.Address = "$J$19:$P$19" Then ' we need "$J$19:$P$19" to make macro work on Delete probably because of merged cells Dim RngTgt As Range: Set RngTgt = Target If Target.Address = "$J$19:$P$19" Then Set RngTgt = Range("J19") If RngTgt.Value = "" Then Let Application.EnableEvents = False Let RngTgt.Value = "(Select Here)" Let Range("R19").Value = "" Let Application.EnableEvents = True Let RngTgt.Font.Color = 10855845 Range("R19").Validation.Delete Range("R19").ClearComments ElseIf RngTgt.Value = "Nuclear Family" Or RngTgt.Value = "Joint Family" Then Let Application.EnableEvents = False Let Range("R19").Value = "(Remarks if any...)" Let Application.EnableEvents = True Let Range("R19").Font.Color = 10855845 Let RngTgt.Font.Color = 6751362 Range("R19").Validation.Delete Range("R19").Select Range("R19").ClearComments ElseIf RngTgt.Value = "Single-Parent Family" Then Let Application.EnableEvents = False Let Range("R19").Value = "(Select Reason for Single-Parent)" Let Application.EnableEvents = True Let Range("R19").Font.Color = 10855845 Let RngTgt.Font.Color = 6751362 With Range("R19").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Expired {One is No More},Divorced {Separated Legally},Break-Up {Attachment Hampered},Abandonment {Fully Separated},Enter Reason Manually" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Error!" .InputMessage = "" .ErrorMessage = "To enter the reason manually, please select the option 'Enter Reason Manually'" .ShowInput = True .ShowError = True End With Range("R19").Select Range("R19").AddComment Range("R19").Comment.Visible = False Range("R19").Comment.Text Text:="Reason for Single-Parent" ElseIf RngTgt.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 RngTgt.Font.Color = 6751362 Range("R19").Validation.Delete Range("R19").Select Range("R19").ClearComments End If ' end of all values of J19 to result in actions Else ' Target is not cell J19 ( or J19:P19 ) End If If Target.Address = "$R$19" Then Let Target.Font.ColorIndex = xlAutomatic If Target.Value = "Enter Reason Manually" Then Target.Validation.Delete Target.Value = "" Else End If Else ' Target is not R19 End If ' Case 3 If Target.Address = "$J$21" Or Target.Address = "$J$21:$P$21" Then Dim RT As Range: Set RT = Target If Target.Address = "$J$21:$P$21" Then Set RT = Range("J21") If RT.Value = "" Then Let Application.EnableEvents = False Let RT.Value = "(Select Here)" Let Range("R21").Value = "" Let Application.EnableEvents = True Let RT.Font.Color = 10855845 ElseIf Not IsEmpty(RT.Value) Then Let Application.EnableEvents = False Let Range("R21").Value = "(Remarks if any...)" Let Application.EnableEvents = True Let Range("R21").Font.Color = 10855845 Let RT.Font.Color = 6751362 Range("R21").Select End If Else End If If Target.Address = "$R$21" Then Let Target.Font.ColorIndex = xlAutomatic End If ' Case 4 If Target.Address = "$J$22" Or Target.Address = "$J$22:$P$22" Then Dim T As Range: Set T = Target If Target.Address = "$J$22:$P$22" Then Set T = Range("J22") If T.Value = "" Then Let Application.EnableEvents = False Let T.Value = "(Select Here)" Let Range("R22").Value = "" Let Application.EnableEvents = True Let T.Font.Color = 10855845 ElseIf Not IsEmpty(T.Value) Then Let Application.EnableEvents = False Let Range("R22").Value = "(Remarks if any...)" Let Application.EnableEvents = True Let Range("R22").Font.Color = 10855845 Let T.Font.Color = 6751362 Range("R22").Select End If Else End If If Target.Address = "$R$22" Then Let Target.Font.ColorIndex = xlAutomatic End If End Sub

I have 2 question here...

1. Case 2 have line

Quote:

Dim RngTgt As Range: Set RngTgt = Target

,Case 3 have line

Quote:

Dim RT As Range: Set RT = Target

, and Case 4 have line

Quote:

Dim T As Range: Set T = Target

I have more than 20 cases which are to be added in the same event macro given above. So is it necessary for me to define the range everytime with a new name (as RngTgt, RT & T)??
Or there is some other easy method available to solve such cases??

2. Case 3 and Case 4 are almost same. The only difference is - Case 3 work on Row 21, while Case 4 work on Row 22. So, is it good to keep them as they are, or there is any way to merge and refine them??

Quote:

I am not sure what the point is about the code lines that you have added like
Range("R19").Select
Range("R19").ClearComments
???

Reason for adding the line Range("R19").ClearComments:
Actually, I've added a line in the Macro which add a comment box in the Cell R19 automatically, after Selecting "Single-Parent Family" in the cell RJ. So, it needs to be cleared on changing selection.

Reason for adding the line Range("R19").Select
I want that when a value is selected in the cell J19, the cursor automatically moves to the cell R19 to get the attention of the user, so that the user can easily enter the remarks, or take a look, at least. (Sorry That I was forget to mention it in my previous reply)

Here is the added code...

Code:

ElseIf RngTgt.Value = "Single-Parent Family" Then Let Application.EnableEvents = False Let Range("R19").Value = "(Select Reason for Single-Parent)" Let Application.EnableEvents = True Let Range("R19").Font.Color = 10855845 Let RngTgt.Font.Color = 6751362 With Range("R19").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Expired {One is No More},Divorced {Separated Legally},Break-Up {Attachment Hampered},Abandonment {Fully Separated},Enter Reason Manually" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Error!" .InputMessage = "" .ErrorMessage = "To enter the reason manually, please select the option 'Enter Reason Manually'" .ShowInput = True .ShowError = True End With Range("R19").Select Range("R19").AddComment Range("R19").Comment.Visible = False Range("R19").Comment.Text Text:="Reason for Single-Parent"

_Row\^Col	AM	AT
16		(Select Here)
16	Nuclear Family	(Remark if any)
17	Joint Family	(Remark if any)
18	Single-Parent Family	(Select Reason)
19		Expired
20		Divorced
21		Break-Up
22		Abandonment
23		Enter Reason Manually
24	Uncategorised	(Please Specify the Case)