Data validataion with VBA/ Named Range

ogo

Board Regular
Joined
Mar 15, 2011
Messages
105
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
You will need a singe name to return the rage, e.g.:
Code:
Sheet1.Range("B" & startRow & ":" & "B" & endRow).Name = "MyRange"

Then in Formula1 you merely refer to:
Code:
=MyRange
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Don't define the start row and end row as names. Define the actual range as a name and refer to that name.
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Hi there,

Maybe this will help?

Code:
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

Board Regular
Joined
Mar 15, 2011
Messages
105

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Board Regular
Joined
Mar 15, 2011
Messages
105

ADVERTISEMENT

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
 

ogo

Board Regular
Joined
Mar 15, 2011
Messages
105
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,276
Members
409,814
Latest member
Leon_Al

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top