Outputting to a specific Excel range removes data validation

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.

Output

Accepted answers

All comments

Qiu

@271828
if you mean filter, based on my test, it can be preserved.

1027-colorado970.zip

echuong1

The filters should be preserved with output. Can you please upload a copy of your workflow and dataset? Or a sample where you're able to replicate?

I can do some testing on my end to see why it's not saving.

271828

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")

Debug.Print spath2

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

Next n

End Sub

carlson1858

Do you know if this was ever fixed?

dasbintee

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!

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703