This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a workflow that outputs data to a specific range in a certain sheet in ~40 workbooks. That works great, however it removes the data validation in that sheet for some reason. Before outputting, there was a dropdown in the "Service Exists" column but that is now gone.
Is this a known issue and if so, is there a workaround? I'll be correcting this particular instance with VBA but it'd be nice to know for the future.
This is still causing issues for me, so here's the VBA I'm using to remedy it. I only have 32 workbooks so I'm using the rather simple method of just listing the workbook names. If you have more, it'd be better to go with a For Each loop or a directory method. The bolded section is how you apply the data validation.
Option Explicit Sub data_validation()
'create loop counting variables Dim x As Integer Dim y As Integer Dim n As Integer
'create worksheet variables Dim wb As Workbook Dim wb1 As Workbook Dim ws As Worksheet Dim ws1 As Worksheet Dim ref_ws As Worksheet
'create save path string variables Dim opath As String Dim opath2 As String
Set ref_ws = Workbooks("Refbook.xlsm").Worksheets("Sheet1")
opath = "\open\file\path\here\test.xlsx"
For n = 2 To 33
opath2 = opath & ref_ws.Cells(n, 2).Value
Set wb = Workbooks.Open(opath2)
Set ws = wb.Worksheets("Sheet1")
With wb .Unprotect Password:="" End With
With ws .Range("A4:A2500").Validation.Add Type:=xlValidateList, Formula1:="<Select>, Y, N" .EnableSelection = xlNoRestriction .Protect Contents:=True, UserInterfaceOnly:=True End With
With wb .Protect Password:="" .Save .Clos End With
I was facing the same issue. I played around to realise that creating data validation with lists using a named range in Excel instead of cell references helps in retaining the drop downs - hope this helps!