Hi
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 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
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
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
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 ) 
Reply With Quote
Bookmarks