Data Validation VBA returning Run Time Error 1004 message

Question

Hi All,

I'm running into a new issue with my VBA to add data validation upon opening a workbook. A portion of my code is below, however the run-time error 1004 is only triggered on the .Add (bolded below):

Private Sub Workbook_Open()

Me.Unprotect Password:="password" 

Sheets("CS Template").Range("G3").Select 

ActiveCell.FormulaR1C1 = "201 - PB OC/MEDIA FURNITURE" 

Sheets("CS Template").Range("H3").Select 

ActiveCell.Formula2R1C1 = "8 - OTTO/BENCHES/STOOLS" 

Sheets("CS Template").Range("I3").Select 

With Selection.Validation 

    .Delete 

    **.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= \_** 

    **xlBetween, Formula1:= \_** 

    **"=OFFSET('Dept Class SClass 11 7 24'!$C$1,MATCH(1,('Dept Class SClass 11 7 24'!$A$2:$A$500=$G3)\*('Dept Class SClass 11 7 24'!$B$2:$B$500=$H3),0),0,COUNTIFS('Dept Class SClass 11 7 24'!$A$2:$A$500,$G3,'Dept Class SClass 11 7 24'!$B$2:$B$500,$H3),1)"** 

    .IgnoreBlank = True 

    .InCellDropdown = True 

    .InputTitle = "" 

    .ErrorTitle = "" 

    .InputMessage = "" 

    .ErrorMessage = "" 

    .ShowInput = True 

    .ShowError = True 

Selection.AutoFill Destination:=Range("I3:I1002") 

Range("I3:I1002").Select

From what I've been seeing on Google, there is a character limit on data validation (256 characters, I believe?), so I was able to shorten my code to 245 characters. I can't use named ranges, because, for some reason, data validation has a hard time reading named ranges with a comparison formula.

I also tried updating .ShowError to False, but that did nothing, so I think that's for a different error lol. There is a unprotect password at the beginning because the sheet is locked once the workbook_open is complete, I figured maybe the protection is the issue (apparently it may not be, but kept it just in case)?

Any help would be greatly appreciated, thank you!!

Answer 1

Thanks!

Column A on the Dept Class SClass 11 7 24 sheet has FURNTURE while G3 on the CS Template sheet has FURNITURE. This causes the error.

Answer 2

Yes! Does this work?:
Stripped CS Template 11.26.24.xlsm

Answer 3

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 4

Hi HansV!

Like this?:
Private Sub Workbook_Open()

Me.Unprotect Password:="password" 

Sheets("CS Template").Range("G3").Select 

ActiveCell.FormulaR1C1 = "201 - PB OC/MEDIA FURNITURE" 

Sheets("CS Template").Range("H3").Select 

ActiveCell.Formula2R1C1 = "8 - OTTO/BENCHES/STOOLS" 

Sheets("CS Template").Range("I3").Select 

With Selection.Validation 

    .Delete 

    Application.DisplayAlerts = False 

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= \_ 

    xlBetween, Formula1:= \_ 

    "=OFFSET('Dept Class SClass 11 7 24'!$C$1,MATCH(1,('Dept Class SClass 11 7 24'!$A$2:$A$500=$G3)\*('Dept Class SClass 11 7 24'!$B$2:$B$500=$H3),0),0,COUNTIFS('Dept Class SClass 11 7 24'!$A$2:$A$500,$G3,'Dept Class SClass 11 7 24'!$B$2:$B$500,$H3),1)" 

    Application.DisplayAlerts = True 

    .IgnoreBlank = True 

    .InCellDropdown = True 

    .InputTitle = "" 

    .ErrorTitle = "" 

    .InputMessage = "" 

    .ErrorMessage = "" 

    .ShowInput = True 

    .ShowError = True 

Selection.AutoFill Destination:=Range("I3:I1002") 

Range("I3:I1002").Select  

I may have done that wrong, because I received the 1004 error again :/

Answer 5

The error will occur if the formula evaluates to an error with the current values of G3 and H3.

Insert the line

Application.DisplayAlerts = False

abovethe set of lines that begins with .Add Type:=xlValidateList, ...

and

Application.DisplayAlerts = True

below those lines