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
__________________________________________________ __________________________
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...
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!!
Reply With Quote
Originally Posted by Anshu
Bookmarks