Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Populating Data Validation
Hi,
I'm having trouble populating a validation list with my macro, my validation list is on one sheet, whilst the data to populate the validation list is on another sheet. The validation list needs to be populated with the values inside the FilteredProducts Range. My code is as follows: Set AllClients = Range("CustomerSolutions").Offset(1, 0).Resize(Range("CustomerSolutions").Rows.Count Set FilteredProducts = AllClients.Columns(8).SpecialCells(xlCellTypeVisib le) With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=FilteredProducts.Address()" 'Applys a product validation list to the product cell for the particular customer .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Product Required" .InputMessage = "" .ErrorMessage = "Please select a product from the drop down list." & vbCrLf & _ "All rechargeable expenditure as well as non-rechargeable airfares and accommodation costs must be allocated to a product." .ShowInput = True .ShowError = True End With The problem is that the FilteredProducts.Address() is returning an address such as $H$74, but I need to somehow specify the name of the sheet that the address is referring to, e.g. SheetName!$H$74 but I'm not too sure how to do this. I think that might solve the problem but I'm not sure. Help would be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Populating Data Validation
Hi
Data Validation list can't refer to another sheet directly - it is possible only through Named Range. When FilteredProducts is defined as Named Range, then define your data validation list simply as =FilteredProducts. Whenever the range FilteredProducts, or values in it, do change, those changes reflect in your data validation list too. Arvi Laanemets "Simon" wrote in message ... Hi, I'm having trouble populating a validation list with my macro, my validation list is on one sheet, whilst the data to populate the validation list is on another sheet. The validation list needs to be populated with the values inside the FilteredProducts Range. My code is as follows: Set AllClients = Range("CustomerSolutions").Offset(1, 0).Resize(Range("CustomerSolutions").Rows.Count Set FilteredProducts = AllClients.Columns(8).SpecialCells(xlCellTypeVisib le) With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=FilteredProducts.Address()" 'Applys a product validation list to the product cell for the particular customer .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Product Required" .InputMessage = "" .ErrorMessage = "Please select a product from the drop down list." & vbCrLf & _ "All rechargeable expenditure as well as non-rechargeable airfares and accommodation costs must be allocated to a product." .ShowInput = True .ShowError = True End With The problem is that the FilteredProducts.Address() is returning an address such as $H$74, but I need to somehow specify the name of the sheet that the address is referring to, e.g. SheetName!$H$74 but I'm not too sure how to do this. I think that might solve the problem but I'm not sure. Help would be greatly appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a data validation list based on criteria | Excel Discussion (Misc queries) | |||
Populating a Data Validation List | Excel Discussion (Misc queries) | |||
Populating data to other worksheet via data validation | Excel Programming | |||
Populating multiple cells on other sheets via data validation | Excel Programming | |||
Populating a Validation List with SQL | Excel Programming |