Aggiornamento in automatico di convalida dati

Question

In un foglio di Excel 2007 ho creato nella cella A1 una convalida relativa  all'elenco B1:B10. (per es. uno, due,tre,....dieci) Vorrei fare in modo che scrivendo un nuovo valore (non compreso in B1:B10, per es . undici o dodici) questo fosse aggiunto automaticamente nell'elenco e che quindi tale elenco potesse "allungarsi" all'infinito ma sempre automaticamente.

Ho provato con la formula " =SCARTO(ELENCO;0;0;CONTA.VALORI(ELENCO);1)" avendo definito il range con il nome di "elenco", ma ciò mi permette sì di inserire in A1 un valore non in elenco ma non mi aggiorna lo stesso in automatico. Io vorrei non essere costretto a ridefinire l'elenco ogni volta: una volta inserito un valore non in elenco vorrei che questi vi rimanesse definitivamente.

grazie

Answer 1

Ciao Romualdo,

"Più so' e più mi accorgo di non sapere."

o se preferisci

"Il vero sapiente è colui che sa di non sapere."

Quindi non scusarti: per una cosa che posso insegnare ce ne sono miliardi per cui merito di stare dietro la lavagna :-))

Per quanto riguarda la tua domanda: vediamo se ho capito. La convalida (la cella con la convalida) resta nel foglio1 cella A1 mentre l'elenco di convalida va nel foglio 2 colonna A?

Se questo è, incolla questo codice nel modulo di classe del Foglio1

---

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target.Address = "$A$1" Then Exit Sub

    Dim lRiga As Long

    Dim sh As Worksheet

    Dim rng As Range

    With Application

        .DisplayAlerts = False

        .EnableEvents = False

    End With

    Set sh = ThisWorkbook.Worksheets("Foglio2")

    With sh

        lRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rng = .Range("A1:A" & lRiga).Find(What:=Target.Value, _

            LookIn:=xlValues, _

            LookAt:=xlWhole, _

            SearchOrder:=xlColumns, _

            SearchDirection:=xlNext, _

            MatchCase:=False)

        If rng Is Nothing Then

            'se non lo trovo aggiungo il nuovo valore

            'all'elenco di convalida

            .Range("A" & lRiga + 1).Value = Target.Value

            'Aggiorno il nome

            ThisWorkbook.Names.Add Name:="Elenco", _

                RefersTo:="=" & _

                .Name & "!" & _

                "$A$1:$A$" & _

                lRiga + 1

        End If

    End With

    With Application

        .DisplayAlerts = True

        .EnableEvents = True

    End With

    Set rng = Nothing

    Set sh = Nothing

End Sub

---

David

Answer 2

ringrazio anche Gamberini per la sollecitudine

 

il problema è che per quanto mi serve io dovrò usare un riferimento (intervallo) di convalida in continua evoluzione. 

Per questo motivo a me occorre che l'aggiornamento dell'elenco sia automatico nel momento stesso che io inserisco un nuovo elemento che fino a quel momento era assente.

 

In poche parole:

1- nella cella di convalida deve essere possibile inserire un elemento assente nel corrispondente elenco

2 - in questo stesso elenco, quindi, dovrà risultare presente in futuro anche il nuovo elemento che ho appena inserito

Spero di avere chiarito. Purtroppo non conosco il linguaggio dei codici.

 

Come fare *senza codice* lo abbiamo già postato.

Con il codice.(se elenco e convalida sono su fogli diversi), inserisci questo nel modulo di codice *del foglio* dove hai l'elenco:

Private Sub Worksheet_Deactivate()

    Dim lRiga As Long

    With Me

        lRiga = .Range("B" & .Rows.Count).End(xlUp).Row

        ThisWorkbook.Names.Add Name:="mioElenco", _

            RefersTo:="=" & _

            .Name & "!" & _

            "$B$1:$B$" & _

            lRiga

    End With

End Sub

In questo caso, Foglio1 B1:B(n) l'elenco, Foglio2 colonna A la convalida.

A questo punto ti basterà inserire nuovi valori all'elenco per averli disponibili nella convalida di altri fogli, purchè la convalida sia Elenco e punti al nome dell'elenco, come spiegato nel primo post.

Come inserire il codice nel modulo del foglio dove hai l'elenco, lo trovi qui:

http://www.maurogsc.eu/excel/xlsdoveinserirecodice.aspx

Qui ho postato il file di esempio elenchicodice.xls:

 https://skydrive.live.com/#cid=0361684D94BB851A&id=361684D94BB851A%21169

Answer 3

In un foglio di Excel 2007 ho creato nella cella A1 una convalida relativa  all'elenco B1:B10. (per es. uno, due,tre,....dieci) Vorrei fare in modo che scrivendo un nuovo valore (non compreso in B1:B10, per es . undici o dodici) questo fosse aggiunto automaticamente nell'elenco e che quindi tale elenco potesse "allungarsi" all'infinito ma sempre automaticamente.

Ho provato con la formula " =SCARTO(ELENCO;0;0;CONTA.VALORI(ELENCO);1)" avendo definito il range con il nome di "elenco", ma ciò mi permette sì di inserire in A1 un valore non in elenco ma non mi aggiorna lo stesso in automatico. Io vorrei non essere costretto a ridefinire l'elenco ogni volta: una volta inserito un valore non in elenco vorrei che questi vi rimanesse definitivamente.

 

grazie

 

Capito poco.

Prova a seguire questa procedura *non vb*, altrimenti passiamo al codice.

Mettiamo tu abbia l'elenco in A1:A5 del Foglio1.

• Seleziona le celle A1:A5
• Scheda: Formule
• Pulsante: Definisci nome
• Definisci nome
• Dai un nome all'elenco, ad esempio: mioElenco
• Controlla che il riferimento a foglio e range sia corretto
• Ok

Mettiamo che tu voglia avere la convalida dati nel Foglio2, celle B1:B10

• Seleziona le celle
• Scheda: Dati
• Pulsante: Convalida dati
• In Consenti, seleziona Elenco
• In Origine scrivi =mioElenco (o il nome che hai dato all'elenco, sempre preceduto da =)
• Ok

Se adesso vuoi aggiungere voci al tuo elenco e che le stesse siano visibili nelle celle dove hai la convalida, in Foglio1 (in questo esempio), inserisci le nuove voci *non* in fondo all'elenco preesistente, ma *fra* le voci già presenti. Ad esempio, in questo esempio, seleziona ad esempio la cella A4, click con il tasto dx e seleziona Inserisci, lascia come spunta Sposta le celle in basso. Nella cella inserisci il nuovo valore. Non devi modificare nessun riferimento all'elenco per ciò che riguarda la convalida; nel Foglio2, selezionando una cella dove hai la convalida, troverai già il nuovo inserimento. Ovviamente puoi riordinare a tuo piacimento l'elenco del Foglio1.

Answer 4

Grazie Canapone

ho provato (copiando ed inncollando la tua istruzione) nella finestra Convalida Dati - Sez. Origine ma tutto risponde come se avessi fatto una semplica convalida dati da elenco origine: b1:b10

Answer 5

Ciao,

nella finestra di convalida, selezionato da consenti "elenco", prova ad indicare come origine

=SCARTO($B$1;;;CONTA.VALORI($B$1:$B$1000);1)

Spero sia d'aiuto

Saluti