Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Excel - Convalida subordinata a N livelli

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Excel - Convalida subordinata a N livelli

Postdi Anthony47 » 04/03/20 23:26

Spesso viene chiesto come fare qualora si voglia avere N convalide "subordinate", dove ogni input delimita l'elenco delle voci disponibili sull'elenco successivo.
Esempio facile: Elenco Regioni -->Elenco Province -->Elenco comuni

I metodi sono vari, ma volendo proporre un meccanismo che lavori su un numero indefinito di convalide subordinate bisogna complicarsi leggermente la vita...

1) Presupposto iniziale e' che l'utente abbia un elenco completo delle combinazioni possibili. Nell'esempio Regioni /Province /Comuni vuol dire che e' disponibile un elenco del tipo
Codice: Seleziona tutto
-regione1 provincia1 comune1
-regione1 provincia1 comune2
-...
-regione1 provincia2 comune1
-regione1 provincia2 comune2
-...
-regione1 provincia3 comune1
-regione1 provincia3 comune2
-...
-...
-regione2 provincia1 comune1
-regione2 provincia1 comune2
-...
-regione2 provincia2 comune1
-regione2 provincia2 comune2
-...
-...
-...
-regioneN provinciaN comuneX
-regioneN provinciaN comuneZ


2) Inseriamo nel nostro file un foglio di servizio, e chiamiamolo ZConvalide
Questo foglio sara' usato per creare in modo dinamico le N liste di convalida, corrispondenti a "Intervalli nominati" a cui verra' assegnato il nome Conv1, Conv2, Conv3, ..., ConvN

La creazione di questi contenuti e' demandata a questa Sub Worksheet_SelectionChange, associata al foglio di lavoro su cui sono inserite le convalide subordinate:
Codice: Seleziona tutto
'RIGOROSAMENTE IN TESTA AL MODULO
'Parametri di configurazione
Private Const ConvArea As String = "F8:K8"              '<<< L'area della prima riga sottoposta a convalida subordinata
Private Const ListaSh As String = "FoglioElenchi"       '<<< Foglio con gli elenchi sorgente
Private Const ListArea As String = "B2:G2"              '<<< Prima riga degli elenchi sorgente
Private Const wSh As String = "ZConvalide"              '<<< Il foglio di servizio
Private Const AutoClear As Boolean = True               '<<< True /False: se True azzera celle a dx di cella variata
Private Const AutoSort As Boolean = True                '<<< True /False; se True gli elenchi di convalida vengono Ordinati


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ListaRan As Range, ZConv As Worksheet
Dim TaRow As Long, TaCol As Long, zConvL As Long, zConvH As Long
Dim wArr, fArr(), fAI As Long, JJ As Long, II As Long, flExit As Boolean
Dim rArr, myMatch
'
If Selection.Count > 1 Then Exit Sub
'
Set ListaRan = Sheets(ListaSh).Range(ListArea)      'set del Foglio e prima riga degli elenchi sorgente
Set ZConv = Sheets(wSh)                             'set del Foglio di servizio usato per creare le covalide
'
TaCol = Target.Column                               'Colonna e Riga della Selezione
TaRow = Target.Row
zConvL = Range(ConvArea).Cells(1, 1).Column         'Low e High delle colonne con Convalida
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
'
If Target.Column >= zConvL And Target.Column <= zConvH Then             'Check se siamo nell'area di convalida
    rArr = Cells(TaRow, zConvL).Resize(1, 1 - zConvL + TaCol).Value     'in rArr la colonna "attuale" piu' le "precedenti"
    'in WArr tante colonne quante ne servono per la Convalida da creare:
    wArr = ListaRan.Cells(1, 1).Resize(ListaRan.Cells(1, 1).Offset(10000, 1).End(xlUp).Row, 1 + TaCol - zConvL).Value
    ReDim fArr(1 To UBound(wArr))
    '
    For II = 1 To UBound(wArr)                      'Per ogni riga in WArr
        For JJ = 1 To UBound(wArr, 2) - 1           'per ogni colonna in WArr "precedente" alla "corrente"
            If Len(rArr(1, JJ)) > 0 Then            '..controlla che sia uguale alle Convalide gia' presenti
                If UCase(wArr(II, JJ)) <> UCase(rArr(1, JJ)) Then
                    flExit = True                   'se <> anche 1 sola colonna allora riga da ignorare
                    Exit For
                End If
            End If
            If flExit Then Exit For
        Next JJ
        If Not flExit And Len(wArr(II, JJ)) > 0 Then                'Se riga da prendere in considerazione..
            'memo: JJ ora punta alla colonna "corrente"
            myMatch = Application.Match(wArr(II, JJ), fArr, False)  '..controlla se esiste gia' in fArr
            If IsError(myMatch) Then                                'Se No, viene aggiunta
                fAI = fAI + 1
                fArr(fAI) = wArr(II, JJ)
            End If
        Else
            flExit = False
        End If
    Next II
    'Scrive nel foglio di servizio le liste di convalida e genera Name:
    fAI = fAI + 1
    ReDim Preserve fArr(1 To fAI)
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(10000, 1).ClearContents
    'Se impostato AutoSort:
    If AutoSort Then
        For II = 1 To UBound(fArr) - 1
            For JJ = II To UBound(fArr)
                If UCase(fArr(II)) > UCase(fArr(JJ)) Then
                    wwk = fArr(II)
                    fArr(II) = fArr(JJ)
                    fArr(JJ) = wwk
                End If
            Next JJ
        Next II
    End If
    'Scrive:
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(fAI, 1) = Application.WorksheetFunction.Transpose(fArr)
    'Genera Nomi:
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(fAI, 1).Name = "Conv" & (TaCol - zConvL + 1)
End If
End Sub

I parametri marcati <<< presenti in testa al modulo sono modificabili e servono per adattare il tutto alla vostra situazione:
I parametri ListaSh e ListArea consentono di dichiarare il nome del foglio che contiene l'elenco "sorgente" e le colonne in cui si trovano
Il parametro wSh consente di indicare il nome del foglio di servizio al cui interno saranno create le liste di convalida dinamiche. Si suggerisce di non usare il foglio per altri motivi.
Il parametro AutoSort As Boolean consente di indicare se gli elenchi di convalida creati devono essere ordinati in ordine alfabetico crescente; ovviamente True significa Si, ordinali; mentre False significa Non ordinare, presentali nello stesso ordine in cui si trovano nell'elenco generale.
Noterete anche un parametro AutoClear As Boolean, posizionato su True nel codice iniziale; esso determina se le scelte "subordinate" gia' fatte (quelle a destra della cella che si va a compilare) devono essere azzerate qualora si modifichi una scelta a livello gerarchico superiore. Il valore True e' quello piu' logico.

La cancellazione delle scelte subordinate e' fatta da questa Sub Worksheet_Change:

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range, lRow As Long
'
zConvL = Range(ConvArea).Cells(1, 1).Column             'Low e High delle colonne con Convalida
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
If AutoClear Then                                       'Gestione dell'Autoclear
    Application.EnableEvents = False
    For Each myC In Target
        If myC.Column >= zConvL And myC.Column < zConvH And myC.Row >= Range(ConvArea).Cells(1, 1).Row Then
            myC.Offset(0, 1).Resize(1, zConvH - myC.Column).ClearContents
        End If
    Next myC
    Application.EnableEvents = True
End If
End Sub


Tutto il codice va messo nel "modulo vba" del foglio su cui sono posizionate le convalide; per arrivarci rapidamente, partendo da Excel:
-tasto dx sul tab col nome del foglio; scegli Visualizza codice
-copia il codice e incollalo nella finestra del vba che con tale procedura e' stata aperta

Nel caso che il foglio gia' contenga una Sub Worksheet_SelectionChange o una Sub Worksheet_Change allora il nuovo codice deve essere integrato col preesistente, cosa che richiede l'esame del codice gia' presente.

3) sara' ora sufficiente che sul foglio di lavoro sia creata un'area dove la prima colonna (non deve essere necessariamente la A, vedi parametro ConvArea) abbia una "convalida da Elenco" con Origine=Conv1; la colonna adiacente avra' una convalida sull'elenco =Conv2; e cosi' via fino alla colonna N

A questo punto, selezionando la prima colonna con convalida dovreste avere la lista di convalida gerarchicamente piu' alta; sulle colonne successive la lista di convalida sara' tarata sulle scelte precedenti

Il file in cui questa soluzione e' dimostrata e' scaricabile qui:
https://www.dropbox.com/s/qafalpayyz9gu ... .xlsm?dl=0

Il foglio con le possibili combinazioni si chiama FoglioElenchi (vedi area in Giallo)
Le convalide sono impostate su FoglioDiLavoro

Il file e' derivato da un lavoro fatto per l'utente BOMAN; vedi viewtopic.php?f=26&t=89543&p=652317#p652306 e messaggi successivi

Le colonne in grigio (sia su FoglioDiLavoro che su FoglioElenchi) sono relative al quesito dell'utente boman, non c'entrano con la problematica della convalida subordinata di cui invece parliamo in questa discussione; idem la funzione contenuta in Modulo1 del vba.

L'adattamento di quanto qui illustrato e il vostro ambiente dovrebbe esserer fatto tutto tramite i parametri di configurazione marcati <<< e presenti in testa al codice.

Operativamente:
-aggiungete il foglio ZConvalide
-inserite nel "Modulo vba" del foglio su cui devono comparire le Convalide tutto il codice qui presentato
-personalizzate i parametri di configurazione, in particolare ConvArea As String, ListaSh e ListArea.
-senza aver ancora impostato le convalide, selezionate una alla volta una cella per ogni colonna destinata a contenere le convalide (vedi parametro ConvArea As String); questo serve a generare gli intervalli, anche se con valori inutili
-ora potete impostare le convalide da "Elenco" con origine pari a, in sequenza, =Conv1, =Conv2, etc etc

Spero che sia di vostro interesse...
Avatar utente
Anthony47
Moderatore
 
Post: 16956
Iscritto il: 21/03/06 16:03
Località: Ivrea

Sponsor
 

Re: Excel - Convalida subordinata a N livelli

Postdi Anthony47 » 10/05/20 18:16

Non ci avevo mai fatto caso: se in un elenco di convalida sono presenti celle vuote e la convalida avviene tramite un "Intervallo Nominato" associato a quell'area allora, quando si inputa un dato non in elenco, il messaggio di errore potrebbe non comparire; bisognerebbe togliere la spunta alla voce "Ignora celle vuote".
Per inciso, se invece la convalida avviene usando il riferimento diretto a quelle celle (quindi, ad esempio: =Foglio3!A1:A10) allora il messaggio compare anche con "Ignora celle vuote" spuntato.

Ricordatevene quando impostate il vostro foglio per le convalide gerarchiche.

Se ve lo dimenticate e non avete voglia di rivedere le vostre impostazioni (che, per inciso basta fare sulla prima riga e mettendo la spunta sulla voce "Applica le modifiche a tutte le celle aventi le stesse impostazioni"), o comunque non siete sicuri della situazione, allora si puo' usare la seguente macro per settare automaticamente, sulle convalide con Elenco pari a "ConvXX", "Ignora celle vuote" su False:
Codice: Seleziona tutto
Sub IgnBlank()
Dim myC As Range, vType As Long, vForm As String
'
Application.EnableEvents = False
For Each myC In Cells.SpecialCells(xlCellTypeAllValidation)
'myC.Select

    vType = 99
    On Error Resume Next
        vType = myC.Validation.Type
        vForm = myC.Validation.Formula1
    On Error GoTo 0
    If vType = 3 And Left(vForm, 5) = "=Conv" Then
        myC.Validation.IgnoreBlank = False
    End If
    Z = Z + 1
Next myC
Application.EnableEvents = True
End Sub

Va inserito in un Modulo standard del vba e poi eseguito (una sola volta sara' sufficiente, SE per il futuro vi ricorderete di impostare "Ignora celle vuote" = False quando impostate inizialmente le convalide)

La macro e' ora presente nel file dimostrativo linkato nel messaggio precedente

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 16956
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "Excel - Convalida subordinata a N livelli":


Chi c’è in linea

Visitano il forum: Nessuno e 12 ospiti