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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,874
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,689
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,689
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,874
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top