Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
526 views
in Technique[技术] by (71.8m points)

Creating Dynamic validation list using VBA: Object Required

I want to create data validation via VBA for dynamic numbers of rows whereas each row is containing dynamic number of columns. I passed the variable out which indicates the row number on which I want to set a data validation, x is the last column up to which I need to check for validation i.e. I will always start with cell(out, 2) and formula will extend up to (out,x). I tried the following code but it's giving me object required error. I think I am making some mistake in Formula and SomeNamedRange sections of the code. What changes should I make in the code and where I am thinking wrong?

Sub DataValidation(out As Integer, x As Integer, y As Integer)
Sheets("first_sheet").Select                          
ActiveSheet.Range(Cells(out, 2), Cells(out, x)).Name = "SomeNamedRange"
Dim RangeToCheck As Excel.Range
Set RangeToCheck = ActiveSheet.Range(Cells(2, 1), Cells(3, 10))
Dim choice
Set choice = "=SomeNamedRange"
With rngRangeToCheck.Select
    Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=choice
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

Also see the attached picture (Data validation should need be added to the yellow portion).enter image description here

EDIT:

I have made some changes to code as suggested in comments but I am still getting the same error in Set choice = "=SomeNamedRange"

Changed Code is as follows:

Sub DataValidation(out As Integer, x As Integer, y As Integer)
Sheets("first_sheet").Select                            
ActiveSheet.Range(Cells(out, 2), Cells(out, x)).Name = "SomeNamedRange"
Dim RangeToCheck As Excel.Range
Set RangeToCheck = ActiveSheet.Range(Cells(out, 2), Cells(out, x))
Dim choice As String
Set choice = "=SomeNamedRange"
'y is the column number where I want validation i.e. yellow column in picture
With RangeToCheck.Cells(out, y).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=choice
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You get an error because you're trying to use the return value of .Select as an Object. You also don't have a variable declared for rngRangeToCheck, and as such it isn't set to anything. You need to either select the Range first and use the Selection object or just use the Range directly:

With RangeToCheck.Validation
    'Do stuff
    '...
End With   
'Or
RangeToCheck.Select
With Selection.Validation
    'Do stuff
    '...
End With

The second issue is with these lines:

Dim choice
Set choice = "=SomeNamedRange"

You're implicitly declaring 'choice' as a Variant, but you're assigning a String to it using Object syntax. It should be:

Dim choice As String
choice = "=SomeNamedRange"

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...