Ho preso lo spunto da questa richiesta per sviluppare una soluzione di Convalida gerarchica di tipo modulare.
Per utilizzare questo modello:
A) diamo per scontato che sia presente un'area in cui siano elencate le possibili combinazioni dei campi; nel tuo caso corrisponde al contenuto di foglio appoggioC2, colonne B:G
B) inserisci nel tuo file un foglio di servizio che chiami ZConvalide (in realta' il nome potrebbe essere modificato intervenendo sul codice). Questo foglio sara' usato per creare dinamicamente le liste di convalida gerarchiche.
C) sul "modulo vba" del foglio su cui devi usare le convalide gerarchiche (il foglio C2, nel tuo caso) inserisci questo codice:
- Codice: Seleziona tutto
'RIGOROSAMENTE IN TESTA AL MODULO
Private Const ConvArea As String = "F8:K8" '<<< La prima riga sottoposta a convalida gerarchica
Private Const ListaSh As String = "appoggioC2" '<<< Foglio degli 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_Change(ByVal Target As Range)
Dim myC As Range, lRow As Long
'
zConvL = Range(ConvArea).Cells(1, 1).Column
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
If AutoClear Then
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
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
TaRow = Target.Row
zConvL = Range(ConvArea).Cells(1, 1).Column
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
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)
For JJ = 1 To UBound(wArr, 2) - 1
If Len(rArr(1, JJ)) > 0 Then
If UCase(wArr(II, JJ)) <> UCase(rArr(1, JJ)) Then
flExit = True
Exit For
End If
End If
If flExit Then Exit For
Next JJ
If Not flExit And Len(wArr(II, JJ)) > 0 Then
myMatch = Application.Match(wArr(II, JJ), fArr, False)
If IsError(myMatch) Then
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
Per accedere rapidamente all'area giusta del vba:
-tasto dx sul tab col nome del foglio (C2, nel tuo caso)
-scegli Visualizza codice
-rimuovi eventuali Sub Worksheet_Change e Sub Worksheet_SelectionChange gia' presenti
-poi copia il codice e incollalo nel frame presumibilmente vuoto, facendo in modo che la riga "RIGOROSAMENTE IN TESTA AL MODULO" sia effettivamente la prima.
Se il modulo vba non fosse vuoto allora devi valutare la compatibilita' tra le macro presenti e quelle da inserire
Le righe in testa servono per impostare i parametri di lavoro; sono compatibili col tuo file di lavoro, ma all'occorrenza possono essere modificati secondo il significato descritto nei commenti di riga.
D) Dopo aver posizionato il codice, torna al foglio C2 e fai una "spazzolata" delle colonne; questo serve a creare gli "intervalli nominati" da usare per la convalida.
E) A questo punto puoi impostare le convalide:
-seleziona l'area della colonna F e imposta una Convalida per Elenco, e come Origine scrivi =Conv1
-ripeti per la seconda colonne, ma come origine scrivi =Conv2
-ripeti per la terza, quarta.... colonna e come origine scrivi =Conv3, =Conv4, =Conv..., =Conv6
Se finalmente pronto per provare: scegli la prima categoria ad esempio in F8, e in G8 avrai a disposizione solo le categorie "figlie" della prima scelta; e cosi' per le altre colonne verso destra
Nei parametri in testa al codice sono presenti le opzioni AutoClear e AutoSort, ambedue impostate su Vero.
AutoClear = Vero provoca la cancellazione di tutte le scelte soggette a convalida eventualmente presenti alla destra di una cella il cui contenuto venga modificato
AutoSort = Vero provoca un ordinamento crescente dell'elenco di Convalida
Se queste impostazioni non ti piacciono (lo capisco per AutoSort, lo capirei meno per AutoClear), le puoi dichiarare
=False e l'effetto non sara' applicato
Vedi se quanto presentato fa l'effetto richiesto per la convalida.
Quanto ai risultati che vuoi ottenere in colonna L ed M, puoi ricorrere a una formula; in L8 imposta la formula
- Codice: Seleziona tutto
=INDICE(appoggioC2!H$1:H$1111;CONFRONTA($F8&$G8&$H8&$I8&$J8&$K8;appoggioC2!$B$1:$B$1111&appoggioC2!$C$1:$C$1111&appoggioC2!$D$1:$D$1111&appoggioC2!$E$1:$E$1111&appoggioC2!$F$1:$F$1111&appoggioC2!$G$1:$G$1111;0))
Da confermare con la combinazione Contr-Maiusc-Enter, non il solo Enter
Quindi copia L8 i M8; infine copia L8:M8 verso il basso
Prova e fai sapere
Ti avevo detto che avevo lavorato per preparare una soluzione modulare, che voglio inserire nella sezione I Vostri Lavori (
viewtopic.php?f=26&t=109375) ti chiederei quindi l'autorizzazione a usare il tuo file, magari deprivato dei valori di costo, come esempio. Ovviamente inserirei anche il link a questa discussione.
Ciao