Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a specific value into a data validated cell using macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a specific value into a data validated cell using macro
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a specific value into a data validated cell using m
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. "Gary''s Student" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validated Cell Arrow not Appearing | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
Selecting a specific cell (that is data validated) | Excel Programming | |||
be validated or not validated in a cell | Excel Programming | |||
Using macro to add data in specific cell within a table | Excel Programming |