Pagina 1 di 2 12 UltimaUltima
Risultati da 1 a 30 di 36

Discussione: Array in convalida dati



  1. #1
    L'avatar di Textomb
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    47
    Messaggi
    171
    Versione Office
    Excel 2016
    Likes ricevuti
    93
    Likes dati
    11

    Array in convalida dati

    Salve a tutti!
    un problema che pensavo fosse risolvibile come mostrato in seguito ma, mi sbagliavo.
    Spero ci sia qualche dettaglio che mi è sfuggito.
    Altrimenti dovrò ricorrere all'appoggio in un intervallo di celle in un foglio separato. Ma la cosa non mi piace molto e vorrei evitarla.
    La questione:
    Ho bisgno di avere l'elenco dei nomi dei fogli, in convalida dati nella cella selezionata.
    Considerato che i fogli sono una ventina non posso utilizzare una stringa in convalida dati perchè supero i 255 caratteri e la convalida entra in crisi all'apertura successiva del file.
    Per questa ragione volevo appoggiarmi ad una matrice di supporto in modo da passare il riferimento della matrice in convalida dati attraverso il codice postato.
    Ma non riesco a farlo funzionare.
    Avete qualche idea?
    grazie mille!!!
    Codice: 
    Sub ListaFogli()
    Dim M_List() As String
    Dim sh As Worksheet
    Dim n As Long
        ReDim M_List(1 To Worksheets.Count)
            For Each sh In Worksheets
                n = n + 1
                M_List(n) = sh.Name
            Next
            
            ' Aggiungo il nome definito
            Application.Names.Add Name:="my_Lista", RefersTo:=M_List
            
            ' provo ad inserirlo in convalida dati
                With ActiveCell.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:="=my_Lista" '<--- Qui mi da errore!!!
                End With
    
    set sh= Nothing
    
    End Sub

  2. #2

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati

    Ciao Textomb,

    ho provato creando una stringa (credo che siamo sopra i 350 caratteri) e sembra funzionare...

    Codice: 
    Sub ListaFogli()
    Dim M_List() As String
    Dim lista As String
    Dim sh As Worksheet
    Dim n As Long
        ReDim M_List(1 To Worksheets.Count)
            For Each sh In Worksheets
                n = n + 1
                M_List(n) = sh.Name
                lista = lista & sh.Name & ","
            Next
            
            lista = Left(lista, Len(lista) - 1)
            ' Aggiungo il nome definito
            Application.Names.Add Name:="my_Lista", RefersTo:=M_List
            
            ' provo ad inserirlo in convalida dati
                With ActiveCell.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=lista   '<--- Qui mi da errore!!!
                End With
    
    
    Set sh = Nothing
    
    
    End Sub
    ma forse non capito bene quello che ti serviva.

    Comunque ti lascio un file di esempio, fammi sapere.

    Ciao
    Tore
    File Allegati File Allegati

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  3. #3

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati

    [EDIT]

    mi sa che hai ragione....
    chiudendo e riaprendo il file riporta sempre un errore...probabilmente dovuto alla stringa.

    Proverò più tardi :279:

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  4. #4
    L'avatar di Textomb
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    47
    Messaggi
    171
    Versione Office
    Excel 2016
    Likes ricevuti
    93
    Likes dati
    11

    Re: Array in convalida dati

    ciao Tore.
    Ti ringrazio per la risposta.
    c'ho già sbattuto la testa io... fidati. La convalida dati accetta max 255 caratteri.
    Al momento ti funziona. Ma poi, quando riapri il file ti rimanda un errore proprio per questa ragione ed elimina l'elenco in convalida.
    Per questa ragione volevo aggirare la questione utilizzando una matrice abbinata alla gestione dei nomi...

  5. #5
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    Un saluto a tutti...
    Anche se la soluzione non mi piace forse potresti ricaricare la convalida rilanciando la macro all'apertura della cartella excel...


    Inviato dal mio GT-I9105P utilizzando Tapatalk

  6. #6

    L'avatar di Rubik72
    Clicca e Apri
    Data Registrazione
    Dec 2015
    Località
    Cosenza
    Età
    45
    Messaggi
    2808
    Versione Office
    Excel 2013
    Likes ricevuti
    1020
    Likes dati
    979

    Re: Array in convalida dati

    L'errore sta nell'oggetto Name che accetta di tutto ma che richiamato genera errore (provare in "manuale", senza macro, creando un nome e scrivendo qualsiasi cosa come array per verificare). Ma forse c'è un escamotage. Anzichè creare un Name con una matrice dentro per poi essere richiamata dal convalida dati, direttamente richiamare una matrice nel convalida dati.
    Codice: 
    Sub ListaFogli()
    Dim M_List() As String
    Dim sh As Worksheet
    Dim n As Long
        ReDim M_List(1 To Worksheets.Count)
            For Each sh In Worksheets
                n = n + 1
                M_List(n) = sh.Name
            Next
            
            ' Aggiungo il nome definito
            'Application.Names.Add Name:="my_Lista", RefersTo:=M_List
            
            ' provo ad inserirlo in convalida dati
                With ActiveCell.Validation
                    .Delete
                    .Add _
                        Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=Join(M_List, ",") '"=my_lista"
                        
                End With
    
    
    Set sh = Nothing
    
    
    End Sub
    EDIT: Rettifico, genera sempre messaggio di errore a riapertura file.:280:

  7. #7

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati

    Citazione Originariamente Scritto da Rubik72 Visualizza Messaggio
    L'errore sta nell'oggetto Name che accetta di tutto ma che richiamato genera errore (provare in "manuale", senza macro, creando un nome e scrivendo qualsiasi cosa come array per verificare). Ma forse c'è un escamotage. Anzichè creare un Name con una matrice dentro per poi essere richiamata dal convalida dati, direttamente richiamare una matrice nel convalida dati.
    Codice: 
    Sub ListaFogli()
    Dim M_List() As String
    Dim sh As Worksheet
    Dim n As Long
        ReDim M_List(1 To Worksheets.Count)
            For Each sh In Worksheets
                n = n + 1
                M_List(n) = sh.Name
            Next
            
            ' Aggiungo il nome definito
            'Application.Names.Add Name:="my_Lista", RefersTo:=M_List
            
            ' provo ad inserirlo in convalida dati
                With ActiveCell.Validation
                    .Delete
                    .Add _
                        Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=Join(M_List, ",") '"=my_lista"
                        
                End With
    
    
    Set sh = Nothing
    
    
    End Sub
    Ciao Rubik,

    purtroppo avevo già provato questa strada (con "Join"), ma alla fine (trattandosi sempre di una stringa con più di 255 caratteri), chiudendo e riaprendo il file otterresti lo stesso errore di cui parlavamo prima :256: :292:

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  8. #8
    L'avatar di Marius44
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    73
    Messaggi
    3216
    Versione Office
    Excel2010
    Likes ricevuti
    625
    Likes dati
    155

    Re: Array in convalida dati

    Salve a tutti
    scusate per l'intromissione ma vorrei dire, timidamente, la mia.

    Invece di mettere la macro (di Alberto o di Tore) in un modulo standard, provate ad inserirla in Workbooks_Open
    Con questo sotterfugio a me funziona senza alcun problema alla riapertura.
    L'unico inghippo che emerge (ma non mancherà a degli esperti come voi superarlo) che, alla chiusura, chiede se si vuole salvare (ovviamente ha notato il "cambiamento" della eliminazione/creazione del nome definito).

    Fatemi sapere se ho detto corbellerie.
    Ciao,
    Mario

  9. #9

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7146
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2063
    Likes dati
    1300

    Re: Array in convalida dati

    Ciao a tutti,
    ricordo in passato che avevo risolto un problema del genere con le formule spezzando la stringa che superava il numero di caratteri previsti in due (o forse 3) sottostringhe concatenate (&) ... avete provato anche questa strada?
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  10. #10

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati

    Ciao a tutti,

    @Mario
    si, così funziona... ma funziona anche dentro il modulo standard, il problema è alla riapertura del file in quanto "non gli sta bene" la stringa di oltre 255 caratteri.
    Prova col mio file allegato e dopo salva e riaprilo... purtroppo dà sempre quell'errore ed elimina il convalida dati dalla cella.

    @ges
    io non ho provato ma alla fine tutti i "pezzi" non li devi comunque riunire per avere l'elenco completo nel convalida dati?


    Ho continuato a fare prove e cercare in rete ma mi sa che Alberto si deve rassegnare ad utilizzare un range del foglio :223:
    Spero vivamente di essere smentito :246:

    Ciao ragazzi.

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  11. #11
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    Provo ad aggiungere un ulteriore tassellino verso la soluzione...
    quello che pensavo, si avvicina alla soluzione indicata da @Marius44 che saluto.
    Supponiamo che le celle nelle quali sia stata attivata la convalida, vengano identificate con una variabile nome chiamata pippo, inserendo in questa cartella di lavoro il codice seguente:

    Codice: 
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
       Application.Goto Reference:="pippo"
                  With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly
                End With
    End Sub
    
    
    Private Sub Workbook_Open()
    Dim lista As String
    Dim sh As Worksheet
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            ' tolgo l'ultima virgola aggiunta
            lista = Left(lista, Len(lista) - 1)
            ' Aggiungo il nome definito
       Application.Goto Reference:="pippo"
                 With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=lista
                End With
    Set sh = Nothing
    
    
    End Sub
    prima della chiusura si elimina la convalida e dopo la chiusura si ricarica ed il gioco è fatto.
    Ora la macro listafogli se non si vuole farlo a mano dovrebbe essere modificata in modo che ad ogni esecuzione, ridefinisca la variabile nome "pippo" per tenere traccia delle posizioni in cui è applicata la convalida.
    sto provando a farlo, ma sono sicuro che sarete più veloci di me ;)
    saluti
    gg

  12. #12
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    ecco modificherei così la macro per memorizzare nella variabilenome "pippo" ogni cella in cui viene eseguita la macro
    Codice: 
    Option Explicit
    Sub ListaFogli()
    Dim lista As String
    Dim sh As Worksheet
    Dim convalidate As String
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            ' tolgo l'ultima virgola aggiunta
            lista = Left(lista, Len(lista) - 1)
          convalidate = ActiveWorkbook.Names("pippo").RefersToR1C1
                With ActiveCell.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=lista
                End With
                convalidate = convalidate & "," & ActiveSheet.Name & "!" & ActiveCell.Address(, , xlR1C1)
                With ActiveWorkbook.Names("pippo")
                    .Name = "pippo"
                    .RefersToR1C1 = convalidate
                End With
    Set sh = Nothing
    End Sub
    certo, così se una convalida viene eliminata da una cella, bisognerebbe ri-aggiornare anche la variabile nome pippo, altrimenti alla riapertura del file gli viene riassegnata la convalida...:223:

    saluti
    gg
    File Allegati File Allegati

  13. #13
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    ...e per agevolare questa ultima cosa del post precedente, sarebbe opportuno che prima di aggiornare la variabile convalidate, con il nuovo indirizzo della cella in cui si è eseguita la macro per inserire la convalida, bisognerebbe fare un controllo che non sia già presente nella stringa della variabile convalidate.
    Saluti gg

    Inviato dal mio GT-I9105P utilizzando Tapatalk

  14. #14
    L'avatar di Marius44
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    73
    Messaggi
    3216
    Versione Office
    Excel2010
    Likes ricevuti
    625
    Likes dati
    155

    Re: Array in convalida dati

    Dopo la partita torniamo alle cose ... importanti.

    @cromagno
    prima mi funzionava ed adesso mi dà l'errore che dite.
    Penso, però, che con questa piccola aggiunta non dia più il problema. Allego il tuo file "aggiustato".

    Non ho fatto altro che aggiungere una "postilla" un ThisWorkbook :166:
    Codice: 
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        With ActiveCell.Validation
            .Delete
        End With
    End Sub
    Ciao a tutti e aspetto le vostre ... critiche,
    Mario
    File Allegati File Allegati

  15. #15

    L'avatar di Rubik72
    Clicca e Apri
    Data Registrazione
    Dec 2015
    Località
    Cosenza
    Età
    45
    Messaggi
    2808
    Versione Office
    Excel 2013
    Likes ricevuti
    1020
    Likes dati
    979

    Re: Array in convalida dati

    Io metterei la macro che carica la convalida (senza assegnare nome) su apertura file, e la macro che cancella il convalida su chiusura.

    Inviato dal mio GT-I9301I utilizzando Tapatalk

  16. #16
    L'avatar di Marius44
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    73
    Messaggi
    3216
    Versione Office
    Excel2010
    Likes ricevuti
    625
    Likes dati
    155

    Re: Array in convalida dati

    Ciao Rubik
    ma come puoi vedere dal file che ho allegato entrambe le macro sono in This_Workbook, una in Open e l'altra in BeforeSave. Le posto qui a beneficio di chi non vuole scaricare il file.
    Codice: 
    Option Explicit
    
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        With ActiveCell.Validation
            .Delete
        End With
    End Sub
    
    
    Private Sub Workbook_Open()
    Dim M_List() As String
    Dim lista As String
    Dim sh As Worksheet
    Dim n As Long
        ReDim M_List(1 To Worksheets.Count)
            For Each sh In Worksheets
                n = n + 1
                M_List(n) = sh.Name
                lista = lista & sh.Name & ","
            Next
            
            lista = Left(lista, Len(lista) - 1)
            ' Aggiungo il nome definito
            Application.Names.Add Name:="my_Lista", RefersTo:=M_List
            
            ' provo ad inserirlo in convalida dati
                With ActiveCell.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=lista   '<--- Qui mi da errore!!!
                End With
    
    
    Set sh = Nothing
    
    
    End Sub
    Ciao,
    Mario

  17. #17
    L'avatar di dracoscrigno
    Clicca e Apri
    Data Registrazione
    May 2016
    Località
    ferrara
    Età
    41
    Messaggi
    2074
    Versione Office
    office pro 2010
    Likes ricevuti
    440
    Likes dati
    241

    Re: Array in convalida dati

    Prova così:

    Sub ListaFogli()
    Dim M_List() As String
    Dim sh As Worksheet
    Dim n As Long
    ReDim M_List(1 To Worksheets.Count)
    For Each sh In Worksheets
    n = n + 1
    M_List(n) = sh.Name
    Next

    ' Aggiungo il nome definito
    Application.Names.Add Name:="my_Lista", RefersTo:=M_List

    ' provo ad inserirlo in convalida dati
    With ActiveCell.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(M_List, ",") '<--- Qui mi da errore!!!
    End With


    Set sh = Nothing


    End Sub
    La ricerca è passata da Google con "Validation VBA" ed è approtata a StackOverFlow Quì pare funzionare bene.

    P.s.
    Nota che nella parentesi non c'è names("my_lista") ma l' array :)

  18. #18
    L'avatar di Textomb
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    47
    Messaggi
    171
    Versione Office
    Excel 2016
    Likes ricevuti
    93
    Likes dati
    11

    Re: Array in convalida dati

    Vi ringrazio tutti per i suggerimenti.
    @dracoscrigno ---> con il Join converti la matrice in una stringa. Così la convalida funziona al momento. Ma all'apertura successiva del file, avrai il problema che la convalida verrà eliminata se supera i 255 caratteri. Vd. anche intervento n. 6 di Rubik72
    @Marius ed altri amici... Non posso caricare la convalida all'apertura ed eliminarla alla chiusura utilizzando gli eventi Workbook_Open(), e Workbook_BeforeSave... per le peculiarità del mio file. Utilizzo celle che devono contenere elenchi differenti nelle convalide dati. Però l'idea in se non era male.
    Mi sa che dovrò utilizzare un foglio di appoggio e registrare li dentro gli intervalli per inserirli nelle convalide dati...
    Resto in ascolto per eventuali altre idee ed auguro a tutti una buona domenica in pace.

  19. #19
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    Difatti per questo avevo introdotto la variabilenome pippo, per togliere la convalida delle sole celle che creano il problema per poi riassegnarla alla riapertura solo a quelle, in merito invece al controllo di una eventuale eliminazione della convalida da una cella di pippo ho pensato si possa fare con un controllo intersect nel modulo di classe del foglio.
    Chiaramente il tutto è molto più macchinoso dell'avere un foglio d'appoggio. Se ci riesco vi posto comunque la soluzione.

    Inviato dal mio GT-I9105P utilizzando Tapatalk

  20. #20
    L'avatar di dracoscrigno
    Clicca e Apri
    Data Registrazione
    May 2016
    Località
    ferrara
    Età
    41
    Messaggi
    2074
    Versione Office
    office pro 2010
    Likes ricevuti
    440
    Likes dati
    241

    Re: Array in convalida dati

    Citazione Originariamente Scritto da Textomb Visualizza Messaggio
    Vi ringrazio tutti per i suggerimenti.
    @dracoscrigno ---> con il Join converti la matrice in una stringa. Così la convalida funziona al momento. Ma all'apertura successiva del file, avrai il problema che la convalida verrà eliminata se supera i 255 caratteri. Vd. anche intervento n. 6 di Rubik72

    Textomb. scusa. come al solito mi ero perso qualche pezzo di topic per strada.
    Mi sa che tra le varie possibilità, la più elegante sia davvero quella del foglio d' appoggio che, magari, puoi riutilizzare anche per altri scopi nel workbook.

    Magari, lo tieni invisibile per scaramanzia...

    VAbbè...
    Mi sono messo a giocare un pochino pensando a come gestire ordinatamente la cosa... ma ora basta che diventa come i solitari a carte :166:
    File Allegati File Allegati

  21. I seguenti utenti hanno dato un "Like"


  22. #21
    L'avatar di Marius44
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    73
    Messaggi
    3216
    Versione Office
    Excel2010
    Likes ricevuti
    625
    Likes dati
    155

    Re: Array in convalida dati

    Ciao a tutti

    @Textomb
    Non posso caricare la convalida all'apertura ed eliminarla alla chiusura utilizzando gli eventi Workbook_Open(), e Workbook_BeforeSave... per le peculiarità del mio file.
    Ultima prova (giuro) senza foglio d'appoggio (non l'ho realizzata nè provata, solo teorizzata).
    Dunque: creo due nomi definiti; il primo recupera i nomi dei fogli dall'inizio fino alla metà; il secondo dalla metà +1 fino alla fine.
    Finito.
    Salvo il foglio coi due nomi.
    Riapro il foglio coi due nomi e non dovrebbe darmi problemi perchè ogni nome è sotto 255. A questo punto creo un terzo nome unificando i due precedenti. Questo terzo nome definito lo do in pasto alla validazione.
    Faccio il mio lavoro.
    Finisco.
    Salvo e Chiudo il file.

    FERMI.

    Qui, prima di salvare, ELIMINO il terzo nome definito (altrimenti alla riapertura mi crea problemi) e poi salvo.
    Cosa avrò alla riapertura?
    I due nomi definiti che devo riunificare e assegnare a Validation.

    Può andare?
    Aggiungo che ho tentato ma mi sono impappinato alla unificazione dei due nomi definiti per creare il terzo (e non ho neppure tentato di proseguire) ma, secondo me, la cosa è fattibile.

    Attendo fiducioso le vostre risposte. Ciao,
    Mario

  23. #22
    L'avatar di Textomb
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Catania
    Età
    47
    Messaggi
    171
    Versione Office
    Excel 2016
    Likes ricevuti
    93
    Likes dati
    11

    Re: Array in convalida dati

    Mario Buondì.
    Ho cambiato strategia. Abbandono la convalida. Quando la cosa diventa macchinosa non mi piace più.
    Ne risente sempre la manutenzione futura.
    Ho ridisegnato il tutto sfruttando le listbox e adesso va in modo semplice e pulito.
    In ogni caso grazie a tutti per i contributi.
    Anche se una problematica non viene risolta come sperato, è sempre motivo di crescita professionale.
    Buona settimana a Tutti!:286:

  24. #23
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    Citazione Originariamente Scritto da Textomb Visualizza Messaggio
    Mario Buondì.
    Ho cambiato strategia. Abbandono la convalida. Quando la cosa diventa macchinosa non mi piace più.
    Ne risente sempre la manutenzione futura.
    Ho ridisegnato il tutto sfruttando le listbox e adesso va in modo semplice e pulito.
    In ogni caso grazie a tutti per i contributi.
    Anche se una problematica non viene risolta come sperato, è sempre motivo di crescita professionale.
    Buona settimana a Tutti!:286:
    ...credo di aver trovato una soluzione!
    Anche se @Textomb che saluto, ha cambiato strategia, mi farebbe piacere avere un vostro riscontro ed eventualmente suggerimenti per possibili miglioramenti.

    La soluzione prevede due sub in un modulo standard e due nel modulo di classe "questa_cartella_di_lavoro" e l'uso della variabile nome "pippo"

    nel modulo standard:
    Codice: 
    Option Explicit
    Sub ListaFogli()
    Dim lista As String
    Dim pos As Long
    Dim sh As Worksheet
    Dim convalidate As String
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            ' tolgo l'ultima virgola aggiunta
            lista = Left(lista, Len(lista) - 1)
            With ActiveCell.Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=lista
            End With
            
    On Error GoTo 10
            'se pippo non esiste lo creo con il riferimento della cella vedi etichetta 10: altrimenti lo aggiungo alla selezione corrente
            convalidate = ActiveWorkbook.Names("pippo").RefersToR1C1
            On Error Resume Next
            pos = InStr(1, convalidate, ActiveSheet.Name & "!" & ActiveCell.Address(, , xlR1C1), vbTextCompare)
            If Not (pos >= 1) Then convalidate = convalidate & "," & ActiveSheet.Name & "!" & ActiveCell.Address(, , xlR1C1)
            ActiveWorkbook.Names("pippo").RefersToR1C1 = convalidate
            Set sh = Nothing
            Exit Sub
    10:
    ActiveWorkbook.Names.Add Name:="pippo", RefersTo:=ActiveCell
    End Sub
    
    
    Public Sub verifica_convalidate()
        Dim sh As Worksheet
        Dim rng As Range
        On Error Resume Next
        Set rng = ThisWorkbook.Worksheets(ActiveSheet.Name).Range("pippo")
        With rng
        .SpecialCells(xlCellTypeSameValidation).Select
        ActiveWorkbook.Names.Add Name:="pippo", RefersToR1C1:=.SpecialCells(xlCellTypeSameValidation).Cells
        End With
        On Error GoTo 0
     End Sub
    nel modulo di classe "questa_cartella_di_lavoro"

    Codice: 
    Private Sub Workbook_Open()
    Dim lista As String
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            ' tolgo l'ultima virgola aggiunta
            lista = Left(lista, Len(lista) - 1)
            ' Aggiungo il nome definito
    On Error GoTo 10
       Application.Goto Reference:="pippo"
                 With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=lista
                End With
    Set sh = Nothing
    10: Exit Sub
    End Sub
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
       On Error GoTo 10
       Application.Goto Reference:="pippo"
       Call verifica_convalidate
       Application.Goto Reference:="pippo"
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly
                End With
    10:    Exit Sub
    End Sub
    File Allegati File Allegati

  25. I seguenti 2 utenti hanno dato un "Like" a ggratis per questo post:


  26. #24
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    Grazie @Marisus44 per aver apprezzato ;)

    aggiungo una piccola semplificazione:

    nel modulo standard:
    Codice: 
    Option Explicit
    Sub ListaFogli()
    Dim lista As String
    Dim sh As Worksheet
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            lista = Left(lista, Len(lista) - 1)
            With ActiveCell.Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=lista
            End With
            Set sh = Nothing
    
    
    On Error GoTo 10
            ActiveWorkbook.Names.Add Name:="pippo", RefersTo:=Union(Range("pippo"), ActiveCell)
            Exit Sub
    10:
    ActiveWorkbook.Names.Add Name:="pippo", RefersTo:=ActiveCell
    End Sub
    
    
    Public Sub verifica_convalidate()
        On Error Resume Next
            With ThisWorkbook.Worksheets(ActiveSheet.Name).Range("pippo")
            ActiveWorkbook.Names.Add Name:="pippo", RefersToR1C1:=.SpecialCells(xlCellTypeSameValidation).Cells
            End With
        On Error GoTo 0
     End Sub
    in questa_cartella_di_lavoro:
    Codice: 
    Private Sub Workbook_Open()
    Dim lista As String
    For Each sh In Worksheets
        lista = lista & sh.Name & ","
    Next
    lista = Left(lista, Len(lista) - 1)
    Set sh = Nothing
    
    
    On Error GoTo 10
        Application.Goto Reference:="pippo"
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=lista
        End With
    10: Exit Sub
    End Sub
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error GoTo 10
        Application.Goto Reference:="pippo"
            Call verifica_convalidate
            Application.Goto Reference:="pippo"
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateInputOnly
            End With
    10:    Exit Sub
    End Sub

  27. #25

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati

    Ciao a tutti,

    @ggratis
    complimenti per la tenacia (ed il codice naturalmente), però riferendomi a quello detto da textomb e che alla fine è il motivo del perchè non riuscivo a far funzionare nessun metodo usato:

    Citazione Originariamente Scritto da textomb
    Ho bisgno di avere l'elenco dei nomi dei fogli, in convalida dati nella cella selezionata.
    quindi, usando il tuo ultimo file ed aggiungendo :

    Codice: 
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Call ListaFogli
    End Sub
    per poter avere il menu con la lista dei fogli in qualsiasi cella io selezioni (in qualsiasi foglio).... crea sempre lo stesso problema, alla successiva apertura esce "l'odioso errore".

    Forse sto complicando troppo le cose...

    A presto
    Tore

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  28. #26
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati

    Ciao @cromagno,
    così come è scritto il codice non può funzionare in ogni foglio. Intanto perché la variabile pippo, dovrebbe essere definita a livello di foglio e non di cartella (ci deve essere un "pippo" per ogni foglio, per capirsi), e alla disattivazione e riattivazione di un foglio devono essere tolte e ri-assegnate le convalide (cosa che andrebbe fatta alla chiusura e riapertura del file, rispetto all'ultimo foglio attivo.

    La particolarità che dovesse funzionare in ogni foglio non l'avevo presa in considerazione, se si volesse questo andrebbe ricontrollato un po' il tutto.

    La definizione del nome dovrebbe essere qualcosa del tipo
    Codice: 
    ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersToR1C1:=ActiveCell
    e quindi
    Codice: 
    ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersTo:=Union(Range("pippo"), ActiveCell)
    Invece, per avere l'elenco nella cella selezionata, io utilizzerei a livello di foglio

    Codice: 
    Private Sub Worksheet_Change(ByVal Target As Range)
            Target.Select
            Call ListaFogli
    End Sub
    magari con un intersect su un intervallo prestabilito, altrimenti ogni volta che attivo o scrivo qualcosa in una cella mi applica la convalida; anche se penso sia più opportuno assegnare l'esecuzione di listafogli ad una combinazione di tasti o ad un pulsante.
    Ma Textomb non credo avrà problemi, a scegliere la strada a lui più congegnale se volesse perseguire nuovamente questa strada.

    saluti
    gg
    File Allegati File Allegati

  29. #27
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati - come migliorare la gestione errore?

    un saluto a tutti,

    ...quando le cose diventano una sfida con se stessi a volte diventano un problema ;)

    Ho modificato il codice in maniera da farlo funzionare in ogni foglio come diceva Cromagno, ma non riesco a gestire bene l'errore sia all'apertura del file che alla chiusura: all'apertura se non trova il nome pippo, applica la convalida alla cella selezionata eseguendo il codice in rosso; alla chiusura stesso errore, se è selezionata una cella diversa da quella nella quale è stata applicata la convalida senza assegnare alla stessa un nome (non viene eliminata, e all'apertura da errore!).

    Come faccio in caso di errore a fargli riprendere l'esecuzione e fargli saltare un intero blocco di istruzioni, nel caso specifico nel ciclo For Each farlo passare al ciclo successivo e non all'istruzione successiva?

    Saluti
    gg

    questa_cartella_di_lavoro
    Codice: 
    Private Sub Workbook_Open()
    Dim ActSheet As Worksheet
    Application.ScreenUpdating = False
    Set ActSheet = ActiveSheet
    
    
    Dim lista As String
    For Each sh In Worksheets
        lista = lista & sh.Name & ","
    Next
    lista = Left(lista, Len(lista) - 1)
    
    
    For Each sh In Worksheets
    sh.Activate
    On Error GoTo 10
        Application.Goto Reference:="pippo"
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=lista
        End With
    10:    Resume Next
    Next
    Set sh = Nothing
    
    
    ActSheet.Select
    Application.ScreenUpdating = True
    End Sub
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ActSheet As Worksheet
    Application.ScreenUpdating = False
    Set ActSheet = ActiveSheet
    
    
    Dim sh As Worksheet
    For Each sh In Worksheets
    sh.Activate
    On Error GoTo 10
        Application.Goto Reference:="pippo"
            Call verifica_convalidate
            Application.Goto Reference:="pippo"
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateInputOnly
            End With
    10:    Resume Next
    Next
    Set sh = Nothing
    
    
    ActSheet.Select
    Application.ScreenUpdating = True
    End Sub
    Modulo standard
    Codice: 
    Option Explicit
    Sub ListaFogli()
    Dim lista As String
    Dim sh As Worksheet
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            lista = Left(lista, Len(lista) - 1)
            With ActiveCell.Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=lista
            End With
            Set sh = Nothing
    
    
    On Error GoTo 10
            ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersTo:=Union(Range("pippo"), ActiveCell)
            Exit Sub
    10:
    ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersToR1C1:=ActiveCell
    End Sub
    
    
    Public Sub verifica_convalidate()
        On Error Resume Next
            With ThisWorkbook.Worksheets(ActiveSheet.Name).Range("pippo")
            ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersToR1C1:=.SpecialCells(xlCellTypeSameValidation).Cells
            If .SpecialCells(xlCellTypeSameValidation).Address = "" Then ThisWorkbook.Worksheets(ActiveSheet.Name).Names("pippo").Delete
            End With
        On Error GoTo 0
     End Sub
    modulo di classe foglio
    Codice: 
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
            Target.Select
            Call ListaFogli
    End Sub
    File Allegati File Allegati

  30. #28

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati - come migliorare la gestione errore?

    Citazione Originariamente Scritto da ggratis Visualizza Messaggio
    un saluto a tutti,

    ...quando le cose diventano una sfida con se stessi a volte diventano un problema ;)
    Ahhhh quanto ti capisco... :292:

    Oggi mi sono preso una pausa da questa discussione, ma domani ci voglio riprovare :32: :270:

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  31. #29
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: Array in convalida dati - come migliorare la gestione errore?

    Citazione Originariamente Scritto da cromagno Visualizza Messaggio
    Ahhhh quanto ti capisco... :292:
    Oggi mi sono preso una pausa da questa discussione, ma domani ci voglio riprovare :32: :270:
    :274::87:_:18: ce l'ho fatta!!!
    in_questa_cartella_di_lavoro
    Codice: 
    Private Sub Workbook_Open()
    Dim ActSheet As Worksheet
    Application.ScreenUpdating = False
    Set ActSheet = ActiveSheet
    
    
    Dim lista As String
    For Each sh In Worksheets
        lista = lista & sh.Name & ","
    Next
    lista = Left(lista, Len(lista) - 1)
    
    
    For Each sh In Worksheets
        sh.Activate
            On Error Resume Next
                With ThisWorkbook.Worksheets(ActiveSheet.Name).Range("pippo").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=lista
                End With
    Next
    
    
    Set sh = Nothing
    ActSheet.Select
    Application.ScreenUpdating = True
    End Sub
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ActSheet As Worksheet
    Application.ScreenUpdating = False
    Set ActSheet = ActiveSheet
    
    
    Dim sh As Worksheet
    For Each sh In Worksheets
        sh.Activate
            Call verifica_convalidate
            On Error Resume Next
                With ThisWorkbook.Worksheets(ActiveSheet.Name).Range("pippo").Validation
                .Delete
                .Add Type:=xlValidateInputOnly
                End With
    Next
    
    
    Set sh = Nothing
    ActSheet.Select
    Application.ScreenUpdating = True
    End Sub
    nel modulo standard
    Codice: 
    Option Explicit
    Sub ListaFogli()
    Dim lista As String
    Dim sh As Worksheet
            For Each sh In Worksheets
                lista = lista & sh.Name & ","
            Next
            lista = Left(lista, Len(lista) - 1)
            With ActiveCell.Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=lista
            End With
            Set sh = Nothing
    
    
    On Error GoTo 10
            ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersTo:=Union(Range("pippo"), ActiveCell)
            Exit Sub
    10:
    ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersToR1C1:=ActiveCell
    End Sub
    
    
    Public Sub verifica_convalidate()
        On Error Resume Next
            With ThisWorkbook.Worksheets(ActiveSheet.Name).Range("pippo")
            ThisWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:="pippo", RefersToR1C1:=.SpecialCells(xlCellTypeSameValidation).Cells
            If .SpecialCells(xlCellTypeSameValidation).Address = "" Then ThisWorkbook.Worksheets(ActiveSheet.Name).Names("pippo").Delete
            End With
        On Error GoTo 0
     End Sub
    E in ogni foglio in cui è inserito il codice seguente, con un clic o scrivendo nella cella si crea la convalida con l'elenco dei fogli.
    Codice: 
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
            Target.Select
            Call ListaFogli
    End Sub
    File Allegati File Allegati

  32. I seguenti 2 utenti hanno dato un "Like" a ggratis per questo post:


  33. #30

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4019
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: Array in convalida dati - come migliorare la gestione errore?

    Ciao ggratis,
    non ho messo un "mi piace" in quanto non riesco a visualizare/far funzionare il tuo codice (forse un problema col mio Excel).
    Sia aprendo il tuo file sia creandone uno nuovo con i tuoi codici.... mi va in crash e chiude il file. :273:

    Comunque, sembra un bel lavoro :246:

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

Discussioni Simili

  1. [Risolto] Riempire array in un modulo e richiamare lo stesso array su un modulo diverso
    Di Domynix nel forum Domande su Excel VBA e MACRO
    Risposte: 8
    Ultimo Messaggio: 07/01/17, 19:36
  2. Convalida Dati
    Di RAFVEC nel forum Domande su Excel in generale
    Risposte: 4
    Ultimo Messaggio: 12/12/16, 15:21
  3. Popolare un array con dati di altri array
    Di Lcbsm nel forum Domande su Excel VBA e MACRO
    Risposte: 10
    Ultimo Messaggio: 08/03/16, 20:31
  4. convalida dati
    Di Zampa nel forum Domande su Excel in generale
    Risposte: 3
    Ultimo Messaggio: 29/01/16, 12:50
  5. Convalida dati da elenco per dati univoci dinamica
    Di tsunami1978 nel forum Domande su Excel in generale
    Risposte: 8
    Ultimo Messaggio: 27/08/15, 00:52

Permessi di Scrittura

  • Tu non puoi inviare nuove discussioni
  • Tu non puoi inviare risposte
  • Tu non puoi inviare allegati
  • Tu non puoi modificare i tuoi messaggi
  •