Frank:
That works like a charm! You're simply awesome!!!
--
Thanks,
Tom
Hi Tom
try the following code (a little bit tested). Watch the linebreaks:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:E30")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
Select Case Target.Column
Case 3
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Yes,No"
Case 4
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="New
York,Miami,Los Angeles"
Target.Offset(0, 2).Validation.Delete
Target.Offset(0, 2).Validation.Add
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Mike,John,Richard,Tina"
End Select
End With
Case "No", "N/A"
Select Case Target.Column
Case 3
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case 4, 5
.Offset(0, 1).Value = "N/A"
.Offset(0, 1).Validation.Delete
End Select
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
Frank:
Again, thanks for the reply... "we" almost got (I understand that you
haven't had the chance to test it).
What doesn't seem to work yet is:
- Selection of "Yes" in D does not bring up "Mike,John,Richard,Tina".
- Is there a way to NOT show "No" beyond column F
("Mike,John,Richard,Tina"). I am sure I could use white font on
white
background. Doesn't seem very elegant though. Would changing
[Me.Range("C2:G30")] to [Me.Range("C2:F30")] do it?
Also, is there any chance to show either "N/A" or "None" (instead of
"No")
for the columns E & F if "No" is selected in column C?
Based on the header question, "No" is the proper answer for column D
(e.g.
"Do you do this task?"), but "N/A" seems to be more appropriate for
the
header questions in E ("Select the city name!") & F ("Select the
sales rep
name!").
I feel bad asking you for your advice again, but this should be the
final
question for the thread!
--
Thanks,
Tom
Hi
not tested but try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
select case target.column
case 3
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
case 4
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="New
York,Miami,Los Angeles"
.offset(0,1).Validation.delete
.offset(0,1).Validation.add
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Mike,John,Richard,Tina"
end select
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
Frank:
One more follow-up question...
currently, the "YesNo" in column C "drives the other "YesNos" of
column D,
E, F, G.
I tried to make a minor change... unsuccessfully though... let's
say:
1. "YesNo" in C drives "YesNo" in D
2. If "No" in D, then "N/A" in E; if "Yes" in D then "New York,
Miami, Los
Angeles" in E.
3. If "No" in D, then "N/A" in F; if "Yes" in D then "Mike, John,
Richard,
Tina" in F.
P.S. There's no link between columns E & F... their values are
simply
driven by the "YesNo" selection in D.
Problem... currently, D does not allow to drive E or F. Here's
what
I did
to accomodate the change but it doesn't work yet.
Changes I made (which won't work though):
1. Duplicated "Private Sub Worksheet_Change(ByVal Target As
Range)"
and
renamed "Target" to "TargetC" and "TargetD".
2. Since step #1 did not work, tried the same with "Range"... now
"RangeC"
and "RangeD".
So, my questions are:
How can C drive D... and how can D drive E & F (rather than
"YesNo"... "New
York", etc & "Mike" etc.?
Thanks so much in advance,
Tom
Hi Tom
glad it works for you and thanks for the feedback
--
Regards
Frank Kabel
Frankfurt, Germany
Frank:
This works just fabulously!!! THANK YOU SO MUCH!
--
Tom
Hi Tom
not quite sure what should happen with lets say column E if
you
enter
'No' in C2 but try the following (note: i hardcoded the
list an
do
not
use a named range as you only have two options with 'yes'
and
'No'):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C2:G30")) Is Nothing
Then
Exit
Sub
On Error GoTo CleanUp
'Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
Newsbeitrag
Frank:
Currently, column D is dependent on column C.
First though, I have defined multiple "YesNo" ranges in
another worksheet "SourceData".
Here's what needs to happen:
1. Column C values:
- click on C2... I get the validation list (combo) which
has a source of of "Yes" or "No" from the YesNo-C range
on
worksheet "SourceData".
- if the select value in C2 = "No" then I want to
display "No" in D2.
- if the selected value in C2 = "Yes" then I want to be
able to call the Name Range "YesNo-D".
2. Column D values:
- if "Yes" was selected in C2, the I get options of Yes
or
No from the YesNo-D validation list.
- here again, if selected "No" value in D2 (from the
combo
drop-down box) then E2 should automatically get "No" as
wel.
- Otherwise, I get the validation list with the "Yes"
or "No" option in E2.
and so on...
Not only is this parent | child | grandchild relationship
true for the Row 2, I also must be able to do this for a
larger range... let's say all the way to row 200.
Any additonal feedback would really help me!!!
Thanks,
Tom
-----Original Message-----
Hi Tom
first: What does not work? Do you get an error message?
For the second part: I'm a little bit confused what
you're trying to
achieve. Each column updates the adjacent one? Post some
example rows
(plaint text - no attachments please) and describe your
expected
result.
--
Regards
Frank Kabel
Frankfurt, Germany
Frank:
Thanks so much for your help. I put the code into the
worksheet that
it
doesn't work right now. I'm sure I have made a mistake
here... well,
or
maybe I didn't provide enough or correct info in the
thread. The
initial
info was based on sample data.
Would you mind having another look at the additional
information?
Thanks so
much in advance!!!
****************
Data input/selections are made on
worksheet: "Priorities"
For instance,...
C2 will update D2
C3 will update D3
C4 will update D4
...
C200 will update D200
D2 will update E2
D3 will update E3
D4 will update E4
...
D200 will update E200
E2 will update F2
E3 will update F3
E4 will update F4
...
E200 will update F200
****************
All of the range names reside on worksheet:
"SourceData"
so, currently C2 is referenced to the "YesNo-C" range
on the
SourceData
worksheet
... D2 is referenced to the "YesNo-D" range on the
SourceData
worksheet
... E2 is referenced to the "YesNo-E" range on the
SourceData
worksheet
... and so on
Thanks again. Your help is greatly appreciated!!!
Tom
message
Hi Tom
as said before this can only be achieved with an
event macro.
Neither a
worksheet function nor a user defined function could
achieve this.
Try
the following code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Yes"
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:="Yes,No"
End With
Case "No"
.Offset(0, 1).Value = "No"
.Offset(0, 1).Validation.Delete
Case Is = ""
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Delete
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
I posted a similar question earlier but I believe
that I may not
have
provided sufficient details.
*****************************************
Does anyone know how to create a user-defined
function which will
allow me
to do the following?
1. From a drop-dop box (combo box), select value
of
either "Yes"
or
"No" in
cell A1 (done via validation list)
2. Then, if A1 = "No" automatically populate B1
with "No".
However,
if A1 =
"Yes" then B1 brings up another drop-down box
(validation list)
with
another
"Yes" or "No" option.
3. If A1 is "Yes" (and whatever subsequent value
was selected in
B1)
but A1
is NOW CHANGED to "No", B1 must be updated to
"No".
At this time, I use the formula below
(while "YesNo" is a range
containing
YES & NO options on a 2nd worksheet): =IF
(A1="No","No",YesNo)
PROBLEM:
The function above only works initially. Once the
dependent
Yes/No
value
(based on "Yes" in A1) has been selected in B1,
changing A1 to
"No"
will NOT
update B1 any longer.
Essentially, the Yes/No selection in B1 "wiped
out"
the
originally
stored
function in B1.
Thanks in advance,
Tom
.
