Menu discesa usando convalida dati

Hai un problema con Excel?

Nuova discussione

Menu discesa usando convalida dati
di Francesco (utente non iscritto) data: 27/11/2012 21:18:14
Ciao ragazzi sto lavorando su alcuni dati e cliccando "convalida dati" creo un menu a discesa di un range che definisco.

1) in tale range però ho nomi ripetuti (giustamente) come posso fare affinchè nel menu creato avere voci univoche?

2) se volessi fare la stessa cosa in vba come mi dovrei muovere?
di HarryBosch data: 28/11/2012 18:39:28
Partendo dal punto 1):
per creare un menù a tendina con i valori univoci, bisogna compiere due operazioni:
- innanzitutto bisogna filtrare la colonna con tutti i valori per determinarne quelli univoci, magari utilizzando lo strumento del filtro avanzato, sotto la scheda "dati"; tramite questo filtro è possibile riportare immediatamente le voci univoche o sovrapponendo i dati alla stessa colonna oppure in una nuova posizione, colonna o foglio (d'appoggio) che siano.
- creato l'elenco dei valori univoci possiamo assegnare il nome all'intervallo che verrà richiamato dalla Convalida dati.
Difetti: se aggiungo valori all'elenco originale, devo ripetere la prima operazione per aggiornare l'elenco dei valori univoci (mentre l'intervallo dei nomi univoci può essere anche dinamico, e quindi si aggiornerebbe automaticamente utilizzando una formula appropriata che se vuoi vediamo... )

Se invece utilizziamo il vba, allora possiamo sbizzarrirci e trovare la soluzione che più ci aggrada.
Creiamo direttamente una collection con nomi univoci della colonna interessata e la assegniamo alle celle per la convalida dati. Possiamo legare l'esecuzione ad un evento in modo da averla sempre aggiornata, come ad esempio l'inserimento di un nuovo valore nella colonna di origine.
Hai utilizzato ancora la Collection?
di Francesco (utente non iscritto) data: 28/11/2012 19:04:51
Sono interessato alle collection! ho provato a modificare quella creata da te qualche settimana fa ma con scarsi risultati.
quindi sono interessato a capire il loro funzionamento:
teoricamente si procede cosi:
-stabilisco un range
-filtro il range in ordine alfabetico
-creo la collezione
-scorro i valori del range assegnadoli alla correzione (il controllo dell'errore permette avere valori univoci)
- assegno ad una cella la visualizzazione della collezione (menu tendina)

non riesco ad ottenere nulla

(ritengo le collection estremamente utili!!! è essenziali che mi impadronisca del loro uso)

di HarryBosch data: 28/11/2012 20:11:53
Allora, vediamo questa collection...
Il codice sotto, supponendo che i tuoi dati siano nella colonna A, mi sembra abbastanza chiaro e adattabile.
Eventualmente, da notare quel CStr... ma ho visto che hai già capito il perché del controllo dell'errore
E i primi 4 trattini sono risolti... puoi filtrare il range prima, oppure ordinare i valori all'interno della collection. Se non disturba modificare l'ordine sul foglio si può fare prima di creare la collection con "risparmio" di codice visto che con una riga te la caveresti:
Columns(1).Sort Key1:=[a1], order1:=xlAscending

Manca l'assegnazione al menù a tendina

Sub elenco_univoco()
    Dim elenco As Range, nome As Range
    Dim col As Collection

    Set elenco = Range("A1:A" & [counta(a:a)])
    Set col = New Collection

    On Error Resume Next
    For Each nome In elenco
        col.Add nome.Value, CStr(nome.Value)
    Next

End Sub

di Francesco (utente non iscritto) data: 28/11/2012 21:01:52
che dovrebbe essere il codice seguente (preso da un tuo lavoro):
Worksheets("Foglio1").Select
With Range("a1").Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:=Listaelenco
End With

di HarryBosch data: 29/11/2012 00:35:19
Credo che non sia possibile assegnare direttamente la collection come elenco a discesa.
Bisogna ciclare i valori della stessa collection e costruire una stringa che inseriremo come menù a tendina.
Supponendo sempre di avere il nostro elenco lungo la colonna A, lo ordiniamo, creiamo la collection dei valori univoci e creiamo il menù a tendina nella cella B1 (o a un intervallo)

Sub elenco_univoco()
    Dim elenco As Range, nome As Range
    Dim col As Collection
    Dim valore As Variant
    Dim lista As String

    Columns(1).Sort Key1:=[a1], order1:=xlAscending

    Set elenco = Range("A1:A" & [counta(a:a)])
    Set col = New Collection

    On Error Resume Next
    For Each nome In elenco
        col.Add nome.Value, CStr(nome.Value)
    Next
    
'per ogni valore della collection creo la lista
    For Each valore In col
        lista = lista & valore & ","
    Next
    lista = Replace(lista & "@", ",@", "")
    
'aggiungo la lista alla cella B1 (o a un intervallo)
    With Range("b1").Validation
       .Delete
        .Add Type:=xlValidateList, Formula1:=lista
   End With

End Sub

di Francesco (utente non iscritto) data: 29/11/2012 13:29:49
Cosi come è il codice ordina la colonna ma non crea il menu! (ci litigo un pochino)
di HarryBosch data: 29/11/2012 13:51:49
Non crea il menù a tendina? Nella cella B1?
very strange :)

di Francesco (utente non iscritto) data: 29/11/2012 14:00:44
cosi funziona!

per ordinare colonna escludendo prima riga?

significato di: Replace(lista & "@", ",@", "") ?

Sub elenco_univoco()
    Dim elenco As Range, nome As Range
    Dim col As Collection
    Dim valore As Variant
    Dim lista As String
    Dim rng As Range, ur As Integer
    

    
With Sheets("Foglio1")

ur = .Cells(Rows.Count, 9).End(xlUp).Row
Set rng = Range(.Cells(1, 9), .Cells(ur, 9))

End With

With Sheets("Foglio1")
    Set elenco = rng
    Set col = New Collection

    On Error Resume Next
    For Each nome In elenco
        col.Add nome.Value, CStr(nome.Value)
    Next
    
'per ogni valore della collection creo la lista
    For Each valore In col
        lista = lista & valore & ","
    Next
    lista = Replace(lista & "@", ",@", "")
  End With
'aggiungo la lista alla cella B1 (o a un intervallo)
Sheets("Statistiche").Activate
    With Range("a10").Validation
       .Delete
        .Add Type:=xlValidateList, Formula1:=lista
   End With

End Sub

di Francesco (utente non iscritto) data: 29/11/2012 14:02:02
non guardare riferimenti a fogli e cella li ho già adattati per le mie esigenze ignorando le annotazioni
di Francesco (utente non iscritto) data: 29/11/2012 14:30:56
Allora cosi ordina i dati mantendo la prima riga fissa. In pratica Excel determina l'intestazione .
ma se volessi determinarla io?
Columns(9).Sort Key1:=Range("i1"), Order1:=xlAscending, Header:=xlGuess

cosi però non ottendo dei risultati corretti in pratica dovrei:
ordinare il range voluto in una colonna di appoggio
o
ordinare in ordine alfabetico gli oggetti della collezione.
di HarryBosch data: 29/11/2012 14:37:35
Per togliere l'intestazione dall'ordinamento bisogna dire di considerarla:
Columns(1).Sort Key1:=[a1], order1:=xlAscending, Header:=True

Se vuoi escludere l'intestazione dalla collectio, o setti il Range a partire dalla seconda cella:
Set rng = Range(.Cells(2, 9), .Cells(ur, 9))

oppure escludi il nome dell'intestazione quando aggiungi i valori in collection:
If nome <> "INTESTAZIONE" Then col.Add nome.Value, CStr(nome.Value)

Dov'è che non ottieni i risultati corretti?
di HarryBosch data: 29/11/2012 14:51:58
Mi son dimenticato il metodo Replace:
serve per togliere l'ultima virgola dalla stringa.

Se visualizzi la finestra immediata, e nel codice scrivi
...
Debug.Print lista
lista = Replace(lista & "@", ",@", "")
Debug.Print lista
...

potrai notare la differenza della stringa "lista" prima e dopo il Replace
di Francesco (utente non iscritto) data: 29/11/2012 15:10:32
Non ottengo i dati corretti perchè ordinando diciamo la prima colonna le altre rimangono invariate quindi i valori non sono più allineati o sbaglio?
devo allora :
ordinare tutto il foglio di lavoro in base ad una colonna specifica.
oppure meglio
ordinare la collezione e non la colonna.
di Francesco (utente non iscritto) data: 29/11/2012 15:24:31
ordinare tutto il foglio di lavoro in base ad una colonna specifica: FATTO - (notare Header:=true) ^_^
Range("a:z").Sort Key1:=Range("i1"), Order1:=xlAscending, Header:=true

ordinare la collezione e non la colonna? (per puro desiderio di apprendimento)

di HarryBosch data: 29/11/2012 18:04:23
Vedo che per estendere l'ordinamento a più colonne hai già trovato la strada giusta.
Per ordinare i valori all'interno della collection, riprendendo l'esempio di questo thred, si può fare come sotto

Sub elenco_univoco()
    Dim elenco As Range, nome As Range
    Dim col As Collection
    Dim valore As Variant
    Dim lista As String
    Dim ColOrdinata As Collection, i As Integer, minore, temp

    Set elenco = Range("A2:A" & [counta(a:a)])
    Set col = New Collection

    On Error Resume Next
    For Each nome In elenco
        col.Add nome.Value, CStr(nome.Value)
    Next

    Set ColOrdinata = New Collection
    Do While col.Count <> 0
        minore = col(1)
        temp = 1
        For i = 1 To col.Count
            'con segno inverso > per ordinare dal maggiore
            If col(i) < minore Then
                minore = col(i)
                temp = i
            End If
        Next i
        col.Remove temp
        ColOrdinata.Add minore
    Loop

    For Each valore In ColOrdinata
        lista = lista & valore & ","
    Next
    lista = Replace(lista & "@", ",@", "")

    With Range("b1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=lista
    End With

End Sub

di Francesco (utente non iscritto) data: 29/11/2012 19:22:59
La strada me l'ha illuminata il vostro forum ! continuando la metafora si può dire che adesso cammino da solo
Spero un giorno di riuscire a correre.

adesso voglio esegire delle operazioni per ciascuno voce della collezione.

quindi si stratta solamente di inserire un ciclo For each ... next
(le righe di codice per le operazioni sono già funzionanti.)

Il problema è che non so dove inserire il ciclo prima della "validazione" della cella o in

For Each valore In col
lista = lista & valore & ","
Next

???
di Vecchio Frac data: 29/11/2012 20:20:02
cit. " La strada me l'ha illuminata il vostro forum "
---> da correggersi in: "La strada me l'ha illuminata il nostro forum"

:)
di Francesco (utente non iscritto) data: 29/11/2012 20:52:06
Ciaooooooooooo Vecchio Frac!!!!!!!!!!!!!!!!!!!!
di Vecchio Frac data: 29/11/2012 21:13:34
Che entusiasmo :) grazie!

Se non ho capito male leggendo tutto, la variabile lista va riempita prima della validazione (anche a me è sempre piaciuto quel pezzo di codice).

lista = Replace(lista & "@", ",@", "")
serve a togliere dalla fine di "lista" la virgola spuria che altirmenti rimane appesa e genera un ulteriore elemento finale vuoto.
Equivale a:
lista = left(lista, Len(lista)-1)
di Francesco (utente non iscritto) data: 29/11/2012 21:20:09
Fin li va bene.
seleziono 1 voce dalla lista e faccio partire una macro ! fin qui ok.
adesso voglio che la stessa operazione venga effettuata in automatico su tutti i valori della collezzione.

Qui la domanda dove devo inserire il ciclo for each?
di HarryBosch data: 29/11/2012 22:14:27
@Vecchio Frac
Quel "Replace" che toglie la virgola l'ho imparato proprio da un tuo codice ^_^
Prima mi affidavo al classico Len...

@Francesco
Se vuoi eseguire un'operazione per ogni valore della collection puoi farlo benissimo all'interno del For Each che hai indicato, ovvero quando li memorizzi nella stringa. Dipende poi cosa devi farli fare: se devi modicarne il valore allora prima di legarli alla lista.
For Each valore In ColOrdinata
'quello che voglio fare con il valore....
lista = lista & valore & ","
Next

di Francesco (utente non iscritto) data: 01/12/2012 17:58:39
Ho provato a personalizzare il codice. Ho inserito un ciclo per fare un la somma dei valori uguali.
in pratica voglio sapere quanti sono i valori uguali per ogni valore della collezione(mi interessano altre operazioni il concetto è lo stesso)

collezione range selezionato
a ci sono 5 a
b ci sono 7 b
c ci sono 3 c

ho fatto questo e altri calcoli inserendo (validando) la collezione in b1 e lanciando le macro a seconda del valore. (funziona tutto perfettamente)
vorrei però che ci fosse la modalità automatica.
Pensavo che fosse più facile.

y=2
For Each cella In rng
For Each valore In col            
    If valore = Trim(cella) Then
            tot = tot + 1
        End If
            With Sheets("Statistiche").Activate
                Cells(3, y) = valore
                Cells(4, y) = tot
            End With
            
         y = y + 1
         lista = lista & valore & ","
next
        Next

di HarryBosch data: 01/12/2012 19:04:42
A parte che dovresti invertire i cicli: ogni valore della collection lo controlli con ogni valore dell'intervallo.
Altrimenti passi due volte gli stessi valori!

E comunque due cicli sono inutili, perché utilizzando la worksheetfunction appropriata te la cavi molto semplicemente come sotto.
Per ogni valore della collection conto quante volte appare nell'intervallo precedentemente assegnato.
In questo caso la function corrisponde al "=conta.più.se"

For Each valore In col
        tot = WorksheetFunction.CountIf(elenco, valore)
        With Sheets("Statistiche")
            .Cells(3, y) = valore
            .Cells(4, y) = tot
            y = y + 1
        End With
        lista = lista & valore & ","
Next

di HarryBosch data: 01/12/2012 19:06:57
dove ho scritto "elenco" all'interno della Worksheets, corrisponde all'intervallo precedentemente assegnato, quindi nel tuo esempio era "rng".

di Francesco (utente non iscritto) data: 01/12/2012 19:14:12
Si certo lo avevo considerato (conte se) ma non saprei come esguire le altre operazioni: circa 70 condizioni estratto sotto:

le ho scritte tutte 70 calcolando massimo e minino (con Application.WorksheetFunction) funziona tutto vorrei che al posto di

Trim(x)= trim(cella) inserire le singole voci della collezione
Trim(voci)= trim(cella)

For Each cella In rng

        If Trim(x) = Trim(cella) Then
            tot = tot + 1
        End If
        If Trim(x) = Trim(cella) And Trim(cella.Offset(0, -2).Value) = "casa" Then
            casa = casa + 1
        End If
        If Trim(x) = Trim(cella) And Trim(cella.Offset(0, -2).Value) = "condominio" Then
            condominio = condominio +1
End If

ecc ecc

di HarryBosch data: 01/12/2012 19:27:37
????
o ti sta sfuggendo qualcosa, o forse sono io che non ho ancora capito cosa vuoi fare.
Non serve indicare i valori che vuoi contare ("condominio", "casa" eccc. ) per 70 volte perché ci pensa già il ciclo For each a passarli tutti! E ogni volta che il ciclo passa, ti fornisce quanti valori ha trovato:
tot = WorksheetFunction.CountIf(rng, valore)
dove valore sarà "condominio" al primo passaggio, "casa" al secondo e così via fino a tutte le voci della collection.

Altrimenti allega il file con dei dati fittizi, così magari riesco a farmi un'idea più chiara :)
di Francesco (utente non iscritto) data: 01/12/2012 19:50:43
Aspetta il ciclo passa i valori "fiori"(rosa, tulipano ecc) in un range accanto (offest)
si trovano le specie (x rosa : black baccara, princess monaco ecc)
cosi la collection mi raggruppa in maniera univoca i tipi di fiore a seconda del valore selezionato mi di dice quanti black baccara ci sono, quante princess ecc.

quindi i cicli per specificare ogni casistica per cui adattandolo esempio : (trim(x) sarà sostituito dal valore della collection mentre Trim(cella.Offset(0, -2).Value) sono le specie del fiore)

For Each cella In rng

If Trim(x) = Trim(cella) Then
tot = tot + 1
End If
If Trim(x) = Trim(cella) And Trim(cella.Offset(0, -2).Value) = "black baccara" Then
baccara = baccara + 1
End If
If Trim(x) = Trim(cella) And Trim(cella.Offset(0, -2).Value) = "princess monaco" Then
monaco = monaco +1
End If

ecc ecc
di Francesco (utente non iscritto) data: 02/12/2012 11:25:44
Ho allegato file di prova. Seleziona il genere dei fiori con il menu che mi hai aiutato tu e clicca sul tasto statistica.

VORREI ottenere la stessa cosa in automatico su tutti i valori della collection.
di Francesco (utente non iscritto) data: 06/12/2012 14:39:07
E' da un pochino che appare questo errore in excel! clicco su si e mi disabilita il menu a discesa nella cella b1.
qualche idea?
(ho fatto un pulasante per aggionare l'elenco e si rispristina subito ma l'errore mi scoccia)
di Francesco (utente non iscritto) data: 06/12/2012 14:40:29
dopo aver cliccato sul "si" appare

Caratteristica rimossa: Convalida dati dalla parte /xl/worksheets/sheet1.xml
di HarryBosch data: 07/12/2012 08:56:43
Ma questo errore ti esce con il file che avevi allegato? Perché io non l'ho riscontrato.

Lo riallego e troverai nel modulo2 la routine che conteggia i fiori divisi per colore in base al fiore che sceglierai dall'elenco (in B1).
Ovviamente da adattare a piacere. Verifica un pò se sei vicino al risultato...
di Francesco (utente non iscritto) data: 07/12/2012 13:32:52
modificando i nomi in cella I l'errore sparisce.

sono arrivato anche io ad ottenere lo stesso risultato (in maniera più macchinosa) vorrei che cliccando su "collection per colore" facesse i calcoli in automatico scorrendo tutti i valori della collezione scrivendo progressivamente il riusltato nelle colonne
di Francesco (utente non iscritto) data: 07/12/2012 18:13:49
in maniera più macchinosa è un eufemismo: mi sono fatto un ciclo un tutte le possibili condizioni.

ero pure soddisfatto! poi vedo il tuo lavoro e mi sento un pochino imbecille!
di Vecchio Frac data: 07/12/2012 18:42:22
Ma dai, bisogna essere positivi :)
Se eri soddisfatto di te stesso è già una buona base di partenza.
Purtroppo (o per fortuna) c'è sempre qualcuno che ne sa più di noi, l'importante è saper cogliere quello che ci serve per riutilizzarlo poi, e magari sfoggiarlo ^_^

di Francesco (utente non iscritto) data: 09/12/2012 18:06:47
Ciao Harry ho dato una occhiata al tuo lavoro e mi pare di aver capito che:
In base al valore in b1 (dato dalla collection fatta in precedente) crei una nuova collechion per le tipologie e ne fai il conteggio in base al valore b1.
riporti i valori.

bhe direi che si vede tutta la tua esperianza io ho fatto la stessa cosa ma creando una condizione per ogni tipologia.

quello che chiedevo solo per imparare come posso fare stessa cosa ma per ogni valore delle collezione in b1? (evitando cosi di scegliere manualmente il valore di b1)

For Each valore In col
            For Each cella In rng
                If cella.Value = valore _
                   And cella.Offset(0, 2).Value = [B1] Then
                    tip = tip + 1 ' conteggio tipo
                End If
            Next
            tot = tot + tip ' totale dei tipi fiori
            Cells(r + 4, 4) = valore ' riporta il nome delle tipologie
            Cells(r + 4, 5) = tip ' riporta il conteggio dei valori
            r = r + 1
            tip = 0
        Next
        Cells(3, 4) = [B1] & ": totali vendite"
        Cells(3, 5) = tot
    End With

di HarryBosch data: 13/12/2012 14:41:13
Comunque ottenevi il risultato voluto e già questo è un segno molto positivo.
Gli aggiustamenti e miglioramenti vengono col tempo, provando e riprovando.
E non si smette mai di imparare qualcosa di nuovo: non è detto che quello che propongo sia in assoluto l'idea migliore o più efficace; spesso anch'io mi perdo in strade tortuose per arrivare al risultato voluto.
Questo forum è fonte anche per il sottoscritto di continue illuminazioni ^_^

Ti propongo la versione "collection fiori-1" dove troverai nel modulo3 una routine che controlla i colori di tutti i fiori, come chiedevi nell'ultimo intervento, senza passare dalla selezione in B1.
In sostanza si tratta di creare un'ulteriore collection, che mi trovi tutti i fiori (oltre a quella che hai riportato che crea la collection dei colori); poi si passano tutti i fiori della collection, e per ognuno di essi tutti i colori dell'altra.

Se i fiori sono "fissi"; si potrebbe, invece di generare ogni volta la collection, ottimizzare inserendo tutti i nomi in un array. Una cosa del tipo:
fiori = Array("Rosa", "Tulipano", "Papavero", "Gelsomino")
quindi scorrere l'array e passare la collection dei colori
di Francesco (utente non iscritto) data: 13/12/2012 20:04:57
Grazie Harry (tra l'altro mi sono accorto di averti chiesto stessa cosa in due post di questa discussione: non era mia intenzione )

mi sono preso un attimo di pausa per raccogliere le idee e studiarmi per bene la creazione delle collection (sintassi e passaggi )

Gli arrey sono ancora degli sconosciuti ^_^

finito il periodo di studio mi metto giu con gli userform e gli arrey!
di Vecchio Frac data: 13/12/2012 21:44:36
Francesco cosa sono gli "arrey"?! due volte nelle ultime due frasi ^_^
E mi permetto solo perchè siamo omonimi :P
di Francesco (utente non iscritto) data: 13/12/2012 21:56:56
lol ^_^

Array !!! Array !!!

Oltre alla conseguenze di aver sbattuto più volte la testa contro la parete causa vba , sto mal sopportando l'influenza; spero di riprendermi per Natale

(Vecchio Frac hai libertà di intervenire come quando vuoi )
di Vecchio Frac data: 13/12/2012 22:14:08
Ma a parte gli scherzi perchè ti suonano così misteriosi gli Array? sono semplicemente dei contenitori di dati, con un indice per identificarne gli elementi. Come le normali variabili, solo che invece di sprecare dieci nomi di variabili diverse puoi usare un nome solo e sfruttarne l'indicizzazione.
E' come avere una scatola divisa in scompartimenti.

Invece di scrivere:
var1 = "pippo"
var2 = "topolino"
var3 = "pluto"

puoi scrivere:
var(0) = "pippo"
var(1) = "topolino"
var(2) = "pluto"

e richiamare ogni elemento con un semplice ciclo For:
For i = 0 to 2
msgbox var(i)
Next
(prova a fare lo stesso con le variabili dedicate)

La cosa particolare da ricordare è che ogni array ha indice base zero (cioè il primo elemento parte da zero) se non viene specificato diversamente con la direttiva Option Base 1 (che piace a Harry ma che io sconsiglio).
Gli utilizzi degli Array (o vettori di elementi) sono frequenti e utilissimi. Bè, non sono costrutti potenti come in altri linguaggi ma fanno il loro sporco lavoro ;)
di Francesco (utente non iscritto) data: 15/12/2012 20:23:36
Grazie dell'illuminazione!!

^_^

sai per caso se leggere questo forum fa passare pure l'influenza?
di Vecchio Frac data: 15/12/2012 20:54:31
Sì.
E inoltre ti fa passare il mal di testa, ti rende più di buonumore e non è detto che non ti spunti qualche euro in tasca.

^_^
di Francesco (utente non iscritto) data: 15/12/2012 23:03:01
Corro a Registrarmi allora!!!!!!!!!!!!!!!!!!!!!!
di Vecchio Frac data: 15/12/2012 23:11:50
Questa è, per sè sola, un'ottima notizia :)
Fatto
di Mangusta data: 16/12/2012 09:06:22
Fatto Francesco da oggi è Mangusta ^_^
di HarryBosch data: 17/12/2012 19:01:37
Bene a sapersi ^_^
Buon proseguimento a Mangusta allora...

Vuoi Approfondire?