Creating Dynamic validation list using VBA: Object Required

Question

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

Comintern · Accepted Answer · 2015-06-17 04:50:44Z

1

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"

edited Jun 17, 2015 at 4:50

answered Jun 17, 2015 at 4:44

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Deepak Uniyal Over a year ago

I have assigned the range already here Set RangeToCheck = ActiveSheet.Range(Cells(2, 1), Cells(3, 10)) and also changed the rngRangeToCheck to RangeToCheck but I am still getting the error. As you said I need to select the range first so is it not the way to select range?

Comintern Over a year ago

@DeepakUniyal - Yes. You can't use the return value of .Select for a With clause.

Deepak Uniyal Over a year ago

Ok Thanks. Now I am doing it as you told RangeToCheck.Select With Selection.Validation 'Do stuff '... End With But issue is not solved yet :(

Rory Over a year ago

As you were already told, remove the Set from Set choice = "=SomeNamedRange"

Rory Over a year ago

Assign ActiveSheet.Range(Cells(out, 2), Cells(out, x)).Address to the validation instead of using named ranges

|

Collectives™ on Stack Overflow

Creating Dynamic validation list using VBA: Object Required

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related