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).
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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…