CODEWITHSUNDEEP
excelvba
Cristiano Morresi
Cristiano Morresi

Reputation: 53

Validation list on vba corrupt file

I did some code and at some point i created an array and then use that array to feed a validation list in a cell, all work just fine but when i close the worksheet and then open it an error occur and i have to do some adjustment in order to use the macro again. I read some tips on internet the easy way is to save the worksheet in binary mode, xlsb, the error occur but the worksheet is usable you have just to relaunch the macro.

what i was wondering is, is there a way to deal with this problem once for all?

here the screenshot about the error

enter image description here

here the description

enter image description here

here the code about the list

Sub filtroSwing()

 Dim mezzi As New Collection
 Dim tot As Range

 Set tot = Foglio3.Range("a1:a" & Foglio3.Cells(Rows.Count, 1).End(xlUp).Row)
 On Error Resume Next
  For i = 1 To tot.Rows.Count

     mezzi.Add tot.Cells(i, 1).Value, tot.Cells(i, 1).Value
 Next i

On Error GoTo 0

 Dim lista() As Variant

 ReDim lista(1 To mezzi.Count)


Dim temp As String



For i = 1 To mezzi.Count

    lista(i) = mezzi(i)
Next i


 'ordina

  For i = 1 To mezzi.Count - 1
        For j = i + 1 To mezzi.Count

         If lista(i) > lista(j) Then
               temp = lista(i)
               lista(i) = lista(j)
               lista(j) = temp
          End If
      Next j
  Next i
     

      Foglio7.Range("f1").Validation.Delete
      Foglio7.Range("f1").Validation.Add xlValidateList, Formula1:=Join(lista, ",")


    Foglio6.Range("u22").Validation.Delete
   Foglio6.Range("u22").Validation.Add xlValidateList,Formula1:=Join(lista, ",")

   end sub

thanks in advance

Cristiano

Upvotes: 0

Views: 139

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If Join(lista, ",") > then 255 characters, you will also have problems.

I suggest:

  • create sorted lista as you have, but as a 2D array eg lista(1 to mezzi.count, 1 to 1)
  • write lista to a range on a hidden worksheet.
    • myRange = lista
  • then .Formula1 = myRange

Upvotes: 1

Related Questions