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. |
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. |
All times are GMT +1. The time now is 11:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com