Data validataion with VBA/ Named Range

[ogo]

I was using the following code part for data validation
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$" & startRow & ":$B$" & endRow................

But the data and the drop down are in different sheets. So i had to try named range the variables startRow and endRow . But this is also not working.Is there any method take data from one sheet into drop down in other sheet .

Thanks,
Ogo

 

[Jon von der Heyden]

You will need a singe name to return the rage, e.g.:

Sheet1.Range("B" & startRow & ":" & "B" & endRow).Name = "MyRange"

Then in Formula1 you merely refer to:

=MyRange

 

[GlennUK]

Don't define the start row and end row as names. Define the actual range as a name and refer to that name.

 

[JamesW]

Hi there,

Maybe this will help?

ActiveWorkbook.Names.Add Name:="namedRange", RefersTo:="=Sheet2!$A$" & startRow & ":$A$" & endRow

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=namedRange"
    End With

 

[ogo]

I can define the range in a sheet but i'm not able it to use the data in the range to validate a cell in another sheet.
thanks,
ogo

 

[GlennUK]

I can define the range in a sheet but i'm not able it to use the data in the range to validate a cell in another sheet.
thanks,
ogo

This is something that I have done hundreds of times. Describe exactly what you did, and what happened.

 

[ogo]

What i have is two sheets. One sheet with data, and in a cell on the second sheet i have to display this data. So at first i tried to display it by creating comma separated string but this throwed some 1004 coz it exceeded 255 char. Now i'm trying to use a namedrange to do this...
thanks,
ogo

 

[GlennUK]

Show the code that's failing!

 

[ogo]

ActiveWorkbook.Names.Add Name:="namedRange", RefersTo:="=Sheet2!$B$" & startRow & ":$B$" & endRow
Sheet5.Cells(iRow, iCol).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=namedRange"
End With


Is failing..and the following also,,


Sheet5.Cells(iRow, iCol).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Sheet2!$B$" & sMachine & ":$B$" & lMachine
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
'.ErrorTitle = ""
'.InputMessage = ""
'.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[Jon von der Heyden]

ActiveWorkbook.Names.Add Name:="namedRange", RefersTo:="=Sheet2!$B$" & startRow & ":$B$" & endRow
Sheet5.Cells(iRow, iCol).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=namedRange"
End With

Is failing..and the following also,,

When you say this fails, specifically what error do you get, and where in the code does it break?

After adding the named range, have you gone back into the name manager to confirm that it is there?

Can you post more of the code please, because I can't see what startRow, endRow, iRow and iCol are, or where they are loaded.