Elenchi a discesa

Hai un problema con Excel?

Nuova discussione

Elenchi a discesa
di Fausto (utente non iscritto) data: 23/06/2012
Ciao a tutti
-nella cella a1 del foglioa si deve creare un elenco a discesa che contenga i nomi riportati nel foglio1(pippo,topolino,pluto,caio,sempronio,tizio) per cui si può scegliere cosa inserire.

-nella cella a3 del foglioa si deve creare un elenco a discesa che contenga i nomi dei fogli che compongono la cartella (foglio1, foglio2; foglio3)e come sopra, si possa scegliere quale foglio inserire
di Harrybosch data: 25/06/2012
Ciao fausto
ti interessa la soluzione con l'uso delle formule oppure la soluzione con l'utilizzo del codice vba?
fammi sapere quale delle due vorresti applicare o se ti interessano entrambe
ciao, vanni
di Fausto (utente non iscritto) data: 25/06/2012
Grazie per l'interessamento, mi interessano entrambe le soluzioni.

di Harrybosch data: 25/06/2012
Come accennavo i due quesiti si possono risolvere sia attraverso le formule sia con l'utilizzo del codice vba (che ovviamente risulta essere più raffinato). per la spiegazione mi baso sull'esempio "es.x" che hai allegato.
per quanto riguarda il primo punto, ovvero creare l'elenco a discesa dei nomi riportati nel foglio1, bisogna agire tramite due passaggi:
1°passaggio: assegnare un nome all'intervallo che deve essere inserito nell'elenco a discesa:
- posizionati sul foglio1 e seleziona l'intera riga 1 (elenco dinamico) oppure solo le celle da a1 a f1 (elenco fisso)
- in alto apri la scheda "formule" e clicca su "gestione nomi", quindi "nuovo"
- nella finestrella che si apre assegna il nome all'intervallo (sarà quello precedentemente selezionato), per esempio nomi e conferma con ok, poi chiudi

2°passaggio: assegnare alla cella (o a più celle) il riferimento dell'intervallo:
- apri il foglioa, posizionati sulla cella a1; in alto apri la scheda "dati" e clicca su "convalida dati"
- nella finestrella che si apre, nei criteri di convalida scegli la voce "elenco" dal menù a discesa che trovi
- si aprirà una barra vuota denominata "origine", dove dovrai scrivere il nome che hai assegnato all'intervallo, ovvero =nomi
- premi ok (lascio a te l'esamina dei messaggi di input e di errore che rendono più "professionale" il lavoro ma non sono necessari ai fini che hai richiesto)

a questo punto nella cella a1 troverai l'elenco a discesa con i nomi inseriti nella riga a del foglio1 (se l'elenco è dinamico, ogni nuovo nome aggiunto sulla riga, aggiorna in automatico l'elenco stesso)

il secondo quesito (l'elenco a discesa con i nomi di tutti i fogli della cartella) credo che non sia risolvibile tramite le formule (ma sono ricettivo ad eventuali soluzioni); necessita in ogni caso di un pò di codice, perlomeno per estrarre i nomi dei fogli e metterli in una colonna.
per esempio, in un foglio chiamato "appoggio", si potrebbe inserire questo codice:
sub nome_dei_fogli()
for n = 1 to thisworkbook.worksheets.count
if sheets(n).name <> "appoggio" then
cells(n, 1).value = sheets(n).name
end if
next n
end sub

in modo da avere lungo la colonna a i nomi di tutti i fogli escluso quello d'appoggio
quindi riesegui la procedura precedente, inserendo l'intervallo (colonna a) di questo foglio per creare l'elenco a discesa con i nomi dei fogli.

ps: esiste la possibilità tramite questa formula,
=stringa.estrai(cella("filename");trova("]";cella("filename"))+1;255)
di riportare il nome del foglio attivo in una qualsiasi cella del foglio stesso: il problema è che se la copi su tutti i fogli non mantiene il nome del foglio stesso, ma riporta in tutte l'ultimo aggiornamento

questo sistema è un pò "rustico" e, tutto sommato, visto che in parte si utilizza comunque il vba, tanto vale adoperare direttamente la soluzione con codice che ti riporto sotto.

la soluzione con il vba ti permette di creare una macro che crea in un sol colpo i due elenchi e li posiziona nelle celle a1 e a3. ogni nuovo nome che aggiungi sulla prima riga del foglio1 aggiornerà in automatico l'elenco della cella a1; se invece inserisci un nuovo foglio, devi rieseguire la macro per aggiornare l'elenco dei fogli nella cella a3

ti allego anche i file, uno con le formule e uno con il codice
ciao, vanni

da riportare in un modulo:

Sub Elenco_a_discesa()

Dim ListaNomi As String
Dim ListaFogli As String
Dim Foglio As Object

Range("a1").Select
    ActiveWorkbook.Names.Add Name:="ListaNomi", RefersToR1C1:="='Foglio1'!R1"
   
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:="=ListaNomi"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

For Each Foglio In Sheets
         ListaFogli = ListaFogli & Foglio.Name & ","
    Next Foglio
  
Range("a3").Select
     ListaFogli = Left(ListaFogli, Len(ListaFogli) - 1)
    
     With Selection.Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, Formula1:=ListaFogli
         .IgnoreBlank = True
         .InCellDropdown = True
         .InputTitle = ""
         .ErrorTitle = ""
         .InputMessage = ""
         .ErrorMessage = ""
         .ShowInput = True
         .ShowError = True
     End With

End Sub

di Vecchio frac data: 29/07/2012
"il secondo quesito (l'elenco a discesa con i nomi di tutti i fogli della cartella) credo che non sia risolvibile tramite le formule (ma sono ricettivo ad eventuali soluzioni); necessita in ogni caso di un pò di codice, perlomeno per estrarre i nomi dei fogli e metterli in una colonna."

no, si può fare anche con una formula (si sfrutta una vecchia funzione dei fogli macro, la =info.cartella.di.lavoro con parametro 1). un po' complicata ma fattibile, si deve definire un nome definito dall'utente che punta a questa funzioncina, per ottenere l'elenco dei fogli in cartella sotto forma di matrice, e poi sfruttare =indice per ottenere ogni singolo nome.

"ps: esiste la possibilità tramite questa formula,
=stringa.estrai(cella("filename");trova("]";cella("filename"))+1;255)
di riportare il nome del foglio attivo in una qualsiasi cella del foglio stesso"

attenzione che =cella("filename") non funziona se la cartella di lavoro non è ancora stata salvata su disco.

se l'argomento interessa lo approfondiamo.
penso però che la soluzione tramite poche linee di codice sia quella più flessibile. ovviamente il codice si può ottimizzare :)

di Harrybosch data: 30/07/2012
Ciao francesco...
certo che mi interessa! quando si tratta di imparare qualcosa di nuovo, di approfondire un argomento, o di migliorare le proprie conoscenze sono sempre tutto orecchi.
quindi approfitto della tua disponibilità in modo diretto (visto che ne beneficio sempre anche dalle soluzioni che proponi nelle altre richieste):

- quella funzione che accennavi, la =info.cartella.di.lavoro, non ne ho mai sentito parlare; ho trovato qualcosa della =info nel supporto della microsoft ma non sembra quello a cui ti riferisci; per quanto riguarda matrice e funzione indice le conosco, quindi aspetto tue delucidazioni;

- per quanto riguarda il =cella("filename") non avevo mai pensato al fatto che se il file non viene prima salvato sul disco e quindi con la scelta del relativo percorso, in effetti la formula non riuscirebbe a "pescarlo"! ora mi sembra così ovvio ma senza il tuo suggerimento non avevo mai considerato questo aspetto;

- il codice non era proprio il massimo, dici bene che si poteva ottimizzare; per esempio con la versione che propongo qua sotto, ma se mi illustri qualche ulteriore miglioramento o suggerimento, mi faresti solo che piacere.

grazie ancora
ciao, vanni

Sub Elenco_a_discesa()

    Dim intervallo As Range
    Dim elenco As New Collection
    Dim Listaelenco
    Dim c As Integer
    Dim n As Integer

    Dim ListaFogli As String
    Dim Foglio As Object
    
    'memorizzo i valori della prima riga del Foglio1 per crearmi l'elenco
    With Worksheets("Foglio1").Activate
        Set intervallo = Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
        fine = intervallo.Columns.Count
        For c = 1 To fine
            elenco.Add Cells(1, c).Text
        Next
    End With
    
    'assegno un nome all'elenco
    For n = 1 To fine ' o elenco.Count
        Listaelenco = Listaelenco & elenco(n) & ","
    Next n
    
    'nel foglio2 creo l'elenco a discesa
    Worksheets("Foglio2").Select
    With Range("a1").Validation
        .Delete
        .Add Type:=xlValidateList, _
             Formula1:=Listaelenco
    End With
    
    'nello stesso foglio creo l'elenco a discesa con i nomi di tutti i fogli
    Range("a3").Select
    For Each Foglio In Sheets
        ListaFogli = ListaFogli & Foglio.Name & ","
    Next Foglio
    ListaFogli = Left(ListaFogli, Len(ListaFogli) - 1)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, _
             Formula1:=ListaFogli
    End With
    
End Sub

di Vecchio frac data: 30/07/2012
Di seguito la mia proposta.
l’idea di usare .validation è naturalmente quella giusta.
come vedi però molto codice inutile si può sfrondare, soprattutto quando è implicito.
puoi riferirti ad un intervallo senza necessariamente memorizzarlo, soprattutto quando ti serve al volo e poi non lo referenzi più.
il principio è di economizzare nelle variabili e di sfruttare bene gli oggetti messi a disposizione, tipizzandoli quando si può (evitiamo l'uso di object ai casi necessari, ad esempio importando una treeview in una userform).
probabilmente si può evitare perfino di memorizzare a parte i valori della lista elenco, ma non ho avuto tempo di verificarlo… a dire il vero ho tentato con join ma non ci sono riuscito ;)

per quanto riguarda il discorso di =info.cartella.di.lavoro, adesso non posso risponderti in modo adeguato, rinvio la risposta a stasera quando avrò un attimo di calma. comunque niente di trascendentale… è una funzione delle vecchie macro di excel 4 che è sopravvissuta (non documentata) perchè naturalmente vba è meglio.

Private Sub elenco_a_discesa2()
Dim ac As Range, lista_elenco As Range, choices As String, sh As Worksheet
    
    'si suppone di avere un elenco di nomi dalla cella A2 in giù
    'tipo pippo, pluto, topolino
    'in A1 c'è l'intestazione di colonna, non serve ma è bello metterla ;)
    
    Set lista_elenco = [a1].CurrentRegion.Offset(1, 0).Resize([a1].CurrentRegion.Rows.Count - 1, 1)
    For Each ac In lista_elenco
        choices = choices & ac & ","
    Next
    choices = Replace(choices & "@", ",@", "")
    
    'nella cella C1 ritrovo l'elenco in cella dei nomi immessi dalla cella A2 in giù
    With [c1].Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=choices
    End With
    
    'nella cella E1 ritrovo l'elenco dei fogli della cartella di lavoro
    [e1].Validation.Delete
    choices = ""
    For Each sh In Sheets
        choices = choices & sh.Name & ","
    Next
    choices = Replace(choices & "@", ",@", "")
        
    [e1].Validation.Add Type:=xlValidateList, Formula1:=choices
    
End Sub

di Harrybosch data: 30/07/2012
Niente da dire. come sempre la tua esamina è molto chiara ed efficace.

ti ringrazio ancora
ciao, vanni
Funzioni =info...
di Vecchio frac data: 30/07/2012
Prendendo spunto dall'articolo di rio (ivano chiappa), cui vi rimando anche per scaricare il file di esempio, ricordo che le funzioni =info sono state mantenute per compatibilità con i vecchi fogli macro di excel 4; oggi sono desueti e si utilizzano le istruzioni vba.
si deifnisce un nome con inserisci > nome > definisci, poniamo "infocartella" e come riferimenti non un indirizzo ma la funzione =info.cartella.di.lavoro(1). il parametro 1 serve per ottenere una matrice con i nomi dei fogli del workbook. nel foglio excel dobbiamo specificare =indice(infocartella;n) per ogni foglio di cui dobbiamo recuperare il nome: quindi =indice(infocartella;1) per il primo foglio, =indice(infocartella;2) per il secondo e così via. ovviamente non sapendo a priori quanti fogli possono esserci in una cartella, servirebbe un ciclo, che con le funzioni non abbiamo... ecco che si rende necessario vba :)

se, a causa della misura antispam, non riesco a proporre il link di rio, cercherò di inserire un allegato; in ogni caso potete scrivermi per maggiori delucidazioni.

Vuoi Approfondire?