Setting drop-down list validation by VBA in Excel

Question

I've got a VBA macro to copy two sheets (one a form to collect data, one a sheet with a batch of lookups for the drop-down menus) from a main workbook, populate the new workbook and save it out.

Because the drop-down data validation lists update their links when the sheet is copied, I have to reset the validation to refer to the new Lookups sheet. At the moment, I'm trying this (with varying coordinates, of course):

With wsNew.Cells(19, 5)  ' Display on web schedule
    .Value = wsData.Cells(11, iColCount).Value
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Lookups!$B$2:$B$3"
    .Validation.IgnoreBlank = True
    .Validation.InCellDropdown = True
End With

As soon as it gets to the .Validation.Add Type... line, it breaks with a 1004 ("Application-defined or object-defined error") error.

If there's any way to copy the sheet without it automatically updating the data validation that would be the ideal, but if there isn't, does anyone know what's causing that code to break and how to fix it?

user4039065 · Accepted Answer · 2016-07-28 07:14:27Z

1

Delete any prior data validation before attempting to create a new one.

With wsNew.Cells(19, 5)  ' Display on web schedule
    .Value = wsData.Cells(11, iColCount).Value
    .Validation.DELETE
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Lookups!$B$2:$B$3"
    .Validation.IgnoreBlank = True
    .Validation.InCellDropdown = True
End With

answered Jul 28, 2016 at 7:14

user4039065

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

Collectives™ on Stack Overflow

Setting drop-down list validation by VBA in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related