Rispondi a: [RISOLTO] Formattazione condizionale dato invisibile ;;; bis

[sidModeratore 4 Febbraio 2019 alle 15:21 Post totali: 716]

#7839

Questo l’activate completo

Private Sub UserForm_Activate()
'  Me.CommandButton2.Enabled = True

Dim wb As Workbook
Dim ws As Worksheet
Dim PROMO As Range
'===================================
'NUOVE VARIABILI AGGIUNTE
Dim rClienti As Range
'Dim aElenco As Variant
Dim aElenco() As Variant
Dim vAppoggio As Variant
Dim lX As Long, lY As Long
Dim cl As Range

'=====================================

Set wb = Workbooks("ANAGRAFICA-ANNO.XLS")
Set ws = wb.Sheets("DATI-CONTABILI")
ws.Activate

'NUOVO CODICE AGIUNTO
'===========================================================================

'Set rClienti = ws.Range("F11:F" & ws.Range("F" & Rows.Count).End(xlUp).Row)
 'Set rClienti = ws.Range("F10:F" & ws.Range("F" & Rows.Count).End(xlUp).Row)
  Set rClienti = ws.Range("F9:F" & ws.Range("F" & Rows.Count).End(xlUp).Row)
      
'aElenco = rClienti
For Each cl In rClienti
    If cl.NumberFormat <> ";;;" Then
        ReDim Preserve aElenco(k)
        aElenco(k) = cl.Value
       k = k + 1
    End If
Next cl

aElenco = Application.Transpose(aElenco)

'ORDINO LA MATRICE
For lX = LBound(aElenco) To UBound(aElenco) - 1
    For lY = lX + 1 To UBound(aElenco)
        If aElenco(lX, 1) > aElenco(lY, 1) Then
            vAppoggio = aElenco(lX, 1)
            aElenco(lX, 1) = aElenco(lY, 1)
            aElenco(lY, 1) = vAppoggio
        End If
    Next lY
Next lX
'POPOLO LA COMBO CON I NOMI DEI CLIENTI
Me.ComboBox6.List = aElenco
With Me.ComboBox6
     .Value = (ws.Range("f10:f10000").SpecialCells(xlCellTypeVisible).End(xlDown).Offset(0, 0))
End With

ComboBox6 = ""
CommandButton2.Visible = False
ComboBox6.SetFocus

Set rClienti = Nothing
Set Ws2 = Nothing
Set wb = Nothing
Set ws = Nothing
Set PROMO = Nothing
'===============================================================================

End Sub