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...
