View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein Rick Rothstein is offline
external usenet poster
 
Posts: 5,934
Default How to set a specific value into a data validated cell using macro

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.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200858


"DimensionAV" wrote:

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.