CODEWITHSUNDEEP
vbaexcel
Jordan Davis
Jordan Davis

Reputation: 875

Add Validation to cell with VBA

I am trying to add a validation list to Excel that changes via the user's response to a message box.

This is what I have: 

 If Not Application.Intersect(Me.[B9], ActiveCell) Is Nothing Then
    Worksheets("New_Project_Code_Request").Range("B9").Value = ""
    strYN = MsgBox(Prompt:="Is this Project Code Request for Cerner HS?", Buttons:=vbExclamation + vbYesNo, Title:="Cerner HS Project Code Request")
    If strYN = vbNo Then
        With Range("B9").Validation
       .Delete
       .Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop,Formula1:="=Enterprise!A1:A6")
        End With
    If strYN = vbYes Then Worksheets("Category").Range("E1").Value = 2
End If

I keep getting the error

Compile Error: Expected: =

I don't know what I am missing.

Upvotes: 0

Views: 200

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

There is no need to add the opening and closing parenthesis from this line:

.Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop,Formula1:="=Enterprise!A1:A6")

It should read:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,Formula1:="=Enterprise!A1:A6"

Upvotes: 2

Related Questions