Hi,

I have a problem with Data Validation (Dropdown Lists).

I have created a file that is dynamically creating dropdown lists for multiple cells on the Sheet.

The values for the list are being passed directly (as values), not as an actual range (address) from the sheet:

 

     

 theList = ""
    
    If Worksheets("temp.ws").Range("A2") <> "" Then

      For k = 1 To Worksheets("temp.ws").Range("A1").End(xlDown).Row
      
          theList = theList & ", " & Worksheets("temp.ws").Cells(k, 1).Value
     
      Next 'k


      With theDestination.Offset(3, 1).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=theList
      End With

    End If

 

Everything is working as intended until i save the file and try to reopen it. Upon opening I'm getting message :

"Removed Feature: Data validation from /xl/worksheets/sheet2.xml part"

and all dropdown lists disappear.

 

This is what happens when I save the file in .xlsm format (Excel 2010, macro enabled spreadsheet).

When I save the same file in binary format (.xlsb) I'm getting different message upon opening:

"Repaired Records: Formula from /xl/worksheets/sheet2.bin part"

and most of the dropdown lists are intact, except the ones that had more than 5 items (dropdown arrow is still visible but there is no list showing when pressed).

I have checked all the formulas (of which I have quite a lot on this sheet) and they didn't seem to be anyhow changed/amended.

They are quite complicated but there is not much different functions used,

e.g.:

=IFERROR(SUMIFS('wk1'!$P$2:$P$3000, 'wk1'!$D$2:$D$3000,$A$276,'wk1'!$V$2:$V$3000,$B$278,'wk1'!$A$2:$A$3000,startdate) /SUMIFS(INDIRECT(IF(RIGHT($B$278,5)= "Small","'wk1'!$G$2:$G$3000",IF(RIGHT($B$278,5)="edium" ,"'wk1'!$H$2:$H$3000",IF(RIGHT($B$278,5)="Large","'wk1'!$I$2:$I$3000",IF(RIGHT($B$278,5)="Bulky", "'wk1'!$J$2:$J$3000",1))))),'wk1'!$D$2:$D$3000,$A$276,'wk1'!$V$2:$V$3000,$B$278,'wk1'!$A$2:$A$3000, startdate),0)

 

Any thoughts?

 

Thanks in advance for any ideas.

I'm unable to verify the issue. The file opens correctly after I created the dropdown list with your macro.

Your file could be broken. Try to make a new one from scratch or save the sheets as SYLK files and join them in a new workbook.

Thanks for the answer.

I tried to create it from the scratch with the same result :(

Saving separate sheets as SYLK won't do as there is plenty of relationships between the sheets.

The file is pulling data from MySQL server prior to creating dropdown lists (using ADO objects).

There is 14 sheets total in the workbook of which 4 has dropdown lists.

Value from the filed with dropdown list is an argument in VLOOKUP function.

 

I tried to create spreadsheet with similar conditions and it was working fine:

 

Function GenerateItems(iCount As Integer) As String
 
 If iCount = 0 Then
  GenerateItems = ""
 Else
  GenerateItems = GenerateItems(iCount - 1) & "Item " & iCount & ", "
 End If
  
End Function

Function RandomInt(High As Integer, Low As Integer) As Integer

 RandomInt = Int((High - Low + 1) * Rnd() + Low)

End Function

Sub CreateMultipleDropLists(howManny As Long, aDestination As Range)
 Dim k As Long
 
 With aDestination
  For k = 1 To howManny
   .Offset(-1 + k, 0).Validation.Add Type:=xlValidateList, Formula1:=GenerateItems(RandomInt(1, 20))
   .Offset(-1 + k, 1).Formula = "=VLOOKUP(" & .Offset(-1 + k, 0).Address & ",$D$1:$D$50,1,FALSE)"
 
  Next 'k
 End With
 
End Sub

Sub DropDownMadness()

 With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .DisplayAlerts = False
 End With
 
 
 Call CreateMultipleDropLists(500, Sheet1.Range("B1"))
 Call CreateMultipleDropLists(500, Sheet2.Range("B1"))
 Call CreateMultipleDropLists(500, Sheet3.Range("B1"))
 Call CreateMultipleDropLists(500, Sheet4.Range("B1"))
 Call CreateMultipleDropLists(500, Sheet5.Range("B1"))
 Call CreateMultipleDropLists(500, Sheet6.Range("B1"))
 Call CreateMultipleDropLists(500, Sheet7.Range("B1"))
 
 
 With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .DisplayAlerts = True
 End With
 

End Sub

I'm out of ideas already.

Well, I would have to substitute fancy dropdown list + vlookup with simple table :(

 

Hi,

In order to resolve this issue more quickly, I suggest let's contact our Excel for developers forum.

http://social.msdn.microsoft.com/Forums/en/exceldev/threads

Best Regards,

Nick Wan

• Marked as answer by Nick WanModerator Thursday, August 18, 2011 2:23 AM

Hi,

You need to ensure that before closing the excel file the length of the validation formula does not exceed 255 characters.

Good luck,

Assaf

This worked for me:

   Office button> excel options > trust center > trust center settings> external content>

   -disable all data com 

   -disable automatic update of workbook links