How to set a specific value into a data validated cell using macro

[DimensionAV]

HI

I have a couple of range names ProductQuantity and ProductColor (A15:A12000
and C15:12000 respectively), ProductColor are data valitaded cells with a
list of colors, I want that after entering a value to ProductQuantity the
corresponding row of ProductColor shows the first value of the list.

I belive a have to use Worksheet_Change event but how to get/set the value
on a datalist cell ?

Thanks in advance.

 

[Gary''s Student]

Lets assume that the validation list for column C is H1 thru H3 (containing
the colors: red, green, blue). Then:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A15:A12000")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 2).Value = Range("H1").Value
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Here is a slightly different approach...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Intersect(Target.EntireRow, Range("ProductColor"))
If Not R Is Nothing And Target.Column <> Range("ProductColor").Column Then
Application.EnableEvents = False
R.Value = Range(Mid(R.Validation.Formula1, 2))(1).Value
Application.EnableEvents = True
End If
End Sub

This event code doesn't use any hard-coded ranges. Besides using the named
range for the ProductColor column, it also let's VB get the validation list
of the Target's row. This way, if there are different validation lists in
effect for different sections of the ProductColor range, the correct "first
item" in that particular validation list will be returned. Doing this is
probably over-kill as one would expect only one validation list to be in
effect for the entire range... but, then, who knows.

 

[DimensionAV]

HI Gary

Considering that I have different sets of Colors (In the Colors Sheet), I
use the following formula to define which is element that corresponds to it:

=OFFSET(INDIRECT(SUBSTITUTE($G15,"
","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($G15," ","")&"_Col")),1)

That formula is taken from the following example to make dynamic lists:
http://www.contextures.com/xlDataVal02.html


Would be possible to reproduce that formula in a VB macro or do you think
that there is a better approach to it ?


Thanks.