Problem with Populating Data Validation

[Simon]

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(xlCellTypeVisible)
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.

 

[Arvi Laanemets]

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