I want to add "Data Validation List" by VBA code.
If in column "A" I have 100 rows of data, I would like that in column be will be 100 "combos" with reference to certaing range.
Thanks
I want to add "Data Validation List" by VBA code.
If in column "A" I have 100 rows of data, I would like that in column be will be 100 "combos" with reference to certaing range.
Thanks
Re: Adding "Data Validation List" by VBA code
Thanks, Guys
Re: Adding "Data Validation List" by VBA code
I must make reference to another worksheet.
What I should change in following code?
I have data for validation on Sheet("Group"), range ("$B$1:$B$6" ).
Dim rList As String
rList = "$B$1:$B$6"
With Range("A1:A100")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
Thanks
Re: Adding "Data Validation List" by VBA code
You will need to create a named range for the validation list, I have used ValRange
Re: Adding "Data Validation List" by VBA code
If it's on a different sheet you have to make it a "named range" - Data Validation won't work if the list source is on another sheet unless it's a defined name. You can do this manually or via code... i.e.
Re: Adding "Data Validation List" by VBA code
QuoteI must make reference to another worksheet.
What I should change in following code?
Use a Named Range in the code like;
Range("MyRange")
Re: Adding "Data Validation List" by VBA code
Guys, thanks a lot.
I trully appreciate your help.
Re: Adding "Data Validation List" by VBA code
Three out of three admin can't be wrong (LOL)
Re: Adding "Data Validation List" by VBA code
Hi, I've attempted to add validation to a worksheet using VBA per the instructions above. The code fails at the line which starts with .Add with the message "Run-time error '1004': Application-defined or object-defined error. What am I doing wrong? The macro is running in Excel 2010 Version 14. Here is the code I'm having problems with:
Sheets(1).Name = "Update Sheet Configuration"
Set Csheet = Worksheets("Update Sheet Configuration")
Csheet.Select
Csheet.Cells(1, 1) = "MS Project source field name"
Csheet.Cells(2, 1) = "Renamed field name"
Csheet.Cells(3, 1) = "Column Titles on Update Spread sheet"
Csheet.Cells(4, 1) = "Column Width (0 is hidden)"
'set data validation lookup tables for cells B1:BY1 referencing 'Lookup Tables'!$A$1:'Lookup Tables'!$A$132
'for cells B2:BY2 insert lookup formulas using the following: =LOOKUP(B1,'Lookup Tables'!$A$200:$A$331,'Lookup Tables'!$B$200:$B$331)
ActiveWorkbook.Names.Add Name:="listdata", RefersTo:="=Lookup Tables!$A$1:$A$132"
With Range("B1:BY1")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=listdata" 'error generated by this line
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
Display More
Any help is much appreciated.
Re: Adding "Data Validation List" by VBA code
rgrady
Please start your own post, reference this one if you want.
Don’t have an account yet? Register yourself now and be a part of our community!