CODEWITHSUNDEEP
excelvalidationvba
t.mo
t.mo

Reputation: 263

Where is the application object defined error in the "Add type" line?

Sub Task()
Dim wb, wb1 As Workbook, copyrange, copyrange1, EWSD, Mrg1, Mrg2, Mrg3, Mrg4, SERange, SERange1 As Range, list, str, str1, str2, str3, str4 As String, Rowt As Long
Application.enableEvents = False

    Set wb = ActiveWorkbook
        list = Range("$A$15:$A$18")
        With wb.Sheets("Client_Eligibility Info").Range("C16").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=list
        End With

Upvotes: 0

Views: 24

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

Dim wb, wb1 As Workbook, copyrange, copyrange1, EWSD, Mrg1, Mrg2, Mrg3, Mrg4, SERange, SERange1 As Range, list, str, str1, str2, str3, str4 As String, Rowt As Long

Will declare all variables that do not have a As after it as a variant,

Change all to explicitly declare:

Dim wb as workbook,wb1 as workbook,...

Make sure list is declared as a string

Dim list as String

Then assign the address to the variable:

'Make sure to assign the correct worksheet to this range
list = wb.WorkSheets("Sheet1").Range("$A$15:$A$18").Address

Then change the

Formula1:=list

To a formula.

Formula1:="=" & list

so:

Sub Task()
Dim wb As Workbook, wb1 As Workbook
Dim copyrange As Range, copyrange1 As Range, EWSD As Range
Dim Mrg1 As Range, Mrg2 As Range, Mrg3 As Range, Mrg4 As Range
Dim SERange As Range, SERange1 As Range
Dim list As String, str As String, str1 As String, str2 As String
Dim str3 As String, str4 As String
Dim Rowt As Long

Application.EnableEvents = False

    Set wb = ActiveWorkbook
        list = wb.Worksheets("Sheet1").Range("$A$15:$A$18").Address
        With wb.Worksheets("Client_Eligibility Info").Range("C16").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & list
        End With

Upvotes: 1

Related Questions