Impostare solo un certo tipo di valori numerici in un range di celle

[SemiuccioPartecipante Semiuccio Karma: 1 pt]

Ciao a tutti.

Uso Excel 2019. Il mio quesito è questo:

nel range di celle A5:E10 devono essere presenti solo valori predefiniti quali, ad esempio:
10
15
23
57
82
121
156
181
201
come posso ottenere questo risultato senza usare il menù a tendina ma semplicemente digitando i valori nel range, e facendo in modo che non vengano accettati valori diversi da quelli attesi?

Grazie.

Allego file di esempio
 

Allegati:

You must be logged in to view attached files.

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

Prova cosi':

Private Sub Worksheet_Change(ByVal Target As Range)

     If Intersect(Target, Range("A5:E10")) Is Nothing Then
        Exit Sub
     Else
        If Target.Value <> 10 and <> 15 and <> 23 and <> 57........Then
               msgbox "Valore non ammesso"
            Exit Sub
        End If
     End If

End Sub

[LucaSRPartecipante LucaSR Karma: 15 pts]

Ciao mi chiedo come mai non vuoi usare un menù a tendina? Sarebbe più comodo e darebbe visivamente un input su quali valori sono ammessi. Valori che potresti modificare facilmente da una tabella/range di celle senza dover mettere mani al codice? 

 

[gianfranco55Partecipante gianfranco55 Karma: 91 pts]

ciao

lo trovo strano comunque

seleziona A2

DATI

CONVALIDA DATI

PERSONALIZZATO

=O(A2=10;A2=15;A2=23;A2=57;A2=82;A2=121;A2=156;A2=181;A2=201)

che poi è la stessa cosa che dovresti fare con la macro

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Naturalmente questa soluzione è molto rigida perchè l'utente è limitato ai soli valori hard coded. Stesso ragionamento per la soluzione VBA di Aldo.

L'OP ha detto che "devono essere presenti solo valori predefiniti quali, ad esempio" perciò quel "ad esempio" mi fa venire il sospetto che si tratti di un elenco solo esemplificativo, che può espandersi. Troverei più utile condizionare la convalida ad un elenco dinamico. E magari in XL 2019 c'è qualche formula mirabolante che si adatta a un range espandibile (io conosco solo l'implementazione con SCARTO e CONTA.VALORI).

[LucaSRPartecipante LucaSR Karma: 15 pts]

vecchio frac ha scritto:

L'OP ha detto che "devono essere presenti solo valori predefiniti quali, ad esempio" perciò quel "ad esempio" mi fa venire il sospetto che si tratti di un elenco solo esemplificativo, che può espandersi. Troverei più utile condizionare la convalida ad un elenco dinamico. E magari in XL 2019 c'è qualche formula mirabolante che si adatta a un range espandibile (io conosco solo l'implementazione con SCARTO e CONTA.VALORI).

Era proprio quello che pensavo! 

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

Per renderla flessibile basta creare un foglio dove elencare i numeri consentiti (Io ho ipotizzato di chiamarlo Dati) e al verificarsi dell'evento Worksheet_Change nel range interessato utilizzare il metodo find per cercare il valore del Target nell'elenco creato.

`Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As String, firstAddress As String
Dim C As Object
Dim Uriga As Long

    Application.ScreenUpdating = False
    
    If Intersect(Target, Range("A5:E10")) Is Nothing Then
        Exit Sub
     Else
        X = Target.Value
    End If

    Sheets("Dati").Select
    Uriga = Sheets("Dati").Range("A" & Rows.Count).End(xlUp).Row

    With Sheets("Dati").Range("A1:A" & Uriga)
        Set C = .Find(X, LookIn:=xlValues, LookAt:=xlWhole)

        If Not C Is Nothing Then
            firstAddress = C.Address
    
            Do
                C.Cells.Select
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> firstAddress
    
        Else
            MsgBox "Valore non valido"
            Sheets("Foglio1").Select
            Sheets("Foglio1").Range(Target.Address).Select
        End If

    End With
    
    Sheets("Foglio1").Select
    Application.ScreenUpdating = True

End Sub`

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Aldo Ercolini ha scritto:

al verificarsi dell'evento Worksheet_Change nel range interessato utilizzare il metodo find per cercare il valore del Target nell'elenco creato.

Tutto ok.

Consiglio solo due cose:
1) disabilitare gli eventi (Application.EnableEvents) a inizio routine, ripristinandoli appena prima di ogni uscita dalla routine. Non vedo istruzioni che modificano celle del foglio ma non si sa mai 🙂 
2) riabilitare lo ScreenUpdating appena prima dell'uscita dalla Sub nel test su If Intersect (perchè il refresh dello schermo è stato disabilitato a inizio routine ed è bene riabilitarlo prima di restituire il controllo).

Trovo superflui i Select sullo Sheet Foglio1 perchè la routine si riferisce proprio a questo foglio e non viene eseguita se non modifichi Foglio1, quindi significa che sei già dentro Foglio1 🙂

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

vecchio frac ha scritto:

Trovo superflui i Select sullo Sheet Foglio1 perchè la routine si riferisce proprio a questo foglio e non viene eseguita se non modifichi Foglio1, quindi significa che sei già dentro Foglio1

Non e' proprio cosi'.

Per fare il find sul foglio Dati devo fare il Select del foglio Dati, altrimenti da errore di elemento non trovato nell'insieme.

Quindi non sono piu' sul Foglio1 ma sul foglio Dati.

Quindi, sia per riposizionare il cursore sulla cella nel caso di valore non valido, sia per rendere di nuovo il foglio1 attivo devo fare il select.

[LucaSRPartecipante LucaSR Karma: 15 pts]

Secondo me è più efficiente il metodo Activate, troppi Select rallentano di brutto la velocità di esecuzione delle macro. Detto questo, quando scrivo codice non uso mai Activate o Select, uso o il name code del foglio oppure creo delle variabili tipizzate per rendere il flusso più snello e leggibile 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Aldo Ercolini ha scritto:

Non e' proprio cosi'.

Vero, non avevo letto bene il codice, sono coinvolti due fogli.

Comunque e' ridondante il riferimento al foglio corrente per quanto riguarda il selezionamento della cella. Altrimenti, e' piu' che corretto esprimere sempre in modo qualificato tutti i riferimento ai range.

[scossaPartecipante scossa Karma: 37 pts]

Aldo Ercolini ha scritto:

Per fare il find sul foglio Dati devo fare il Select del foglio Dati, altrimenti da errore di elemento non trovato nell'insieme.

Questo non è assolutamente necessario, basta applicare il metodo find al foglio dati (vedi sotto la sub corretta).

Quindi tutti quei Select si possono eliminare; inoltre 

            Do
                C.Cells.Select
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> firstAddress

trovato un match, fare il loop per vedere se ci sono altre occorrenze è cosa priva di senso.

Questo il tuo codice rivisto alla luce di quanto sopra:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As String
  Dim C As range
  Dim Uriga As Long
  
    
    If Not Intersect(Target, Range("A5:E10")) Is Nothing Then
      X = Target.Value
      Application.ScreenUpdating = False
      With Worksheets("Dati")
        Uriga = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("A1:A" & Uriga)
          Set C = .Find(X, LookIn:=xlValues, LookAt:=xlWhole)
          If C Is Nothing Then
            MsgBox "Valore non valido"
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
          End If
        End With
      End With
      Application.ScreenUpdating = True
  End If
End Sub

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

scossa ha scritto:

Questo il tuo codice rivisto alla luce di quanto sopra

    

[scossaPartecipante scossa Karma: 37 pts]

Poi, volendo cambiare approccio, io farei così:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Dim aValues As Variant
  Dim sValues As String
  
  If Not Intersect(Target, Range("A5:E10")) Is Nothing Then
    aValues = Application.Transpose(Worksheets("Dati").Range("A1").CurrentRegion.Columns(1).Value)
    If InStr("#" & Join(aValues, "#") & "#", "#" & ActiveCell.Value & "#") = 0 Then
      MsgBox "Valore non valido"
      Application.EnableEvents = False
      Target.ClearContents
      Application.EnableEvents = True
    End If
  End If
End Sub

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

scossa ha scritto:

Poi, volendo cambiare approccio, io farei così:

Sinceramente non ci avrei mai pensato....    

[SemiuccioPartecipante Semiuccio Karma: 1 pt]

Ottimo Scossa. Grazie mille. Allora approfitto per porti un'altra domanda.
Posso creare range costituiti da (sempre ad esempio) colonne alternate ? Cerco di spiegarmi meglio.

Posso avere in un range A:A lo stesso tipo di valori che può assumere il range C:C e il range E:E?
Poi avere altri tipi di valori nei range B:B, D:D ed F:F ?

TI ringrazio ancora

 

[SemiuccioPartecipante Semiuccio Karma: 1 pt]

Scusa. Mi riferifo a questo codice. l'altro mi dà sempre  "valore non valido".

[SemiuccioPartecipante Semiuccio Karma: 1 pt]

scossa ha scritto:

Aldo Ercolini ha scritto:

Per fare il find sul foglio Dati devo fare il Select del foglio Dati, altrimenti da errore di elemento non trovato nell'insieme.

Scusa mi riferivo a questo codice, l'altro mi dà sempre "valore non valido"

[alexps81Moderatore alexps81 Karma: 58 pts]

Vista la natura delle soluzioni proposte, sposto questo Thread nella sezione corretta.

 

Per @semiuccio, dovresti aprire una nuova discussione per questa nuova richiesta

Semiuccio ha scritto:

approfitto per porti un'altra domanda. Posso creare range costituiti da (sempre ad esempio) colonne alternate ?

 

[SemiuccioPartecipante Semiuccio Karma: 1 pt]

alexps81 ha scritto:

Vista la natura delle soluzioni proposte, sposto questo Thread nella sezione corretta.

Scusa, in quale sezione hai spostato il Thread?

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

Semiuccio ha scritto:

Scusa. Mi riferifo a questo codice. l'altro mi dà sempre  "valore non valido".

In questa riga

If InStr("#" & Join(aValues, "#") & "#", "#" & ActiveCell.Value & "#") = 0 Then

sostituisci ActiveCell.Value con Target.Value

e per aumentare il range

If Not Intersect(Target, Range("A5:E10","F5:L10", .....)) Is Nothing Then

[scossaPartecipante scossa Karma: 37 pts]

Aldo Ercolini ha scritto:

sostituisci ActiveCell.Value con Target.Value

Perché? nell'evento Worksheet_Change ActiveCell appartiene sicuramente a Target (è esattamente la prima cella selezionata), questo permette di prendere in considerazione solo quella cella, evitando problemi nel caso in cui Target sia un range di più celle.

Per il resto, non ho capito la domanda di @semiuccio.

[Aldo ErcoliniPartecipante Aldo Ercolini Karma: 19 pts]

scossa ha scritto:

Perché?

Perché una volta digitato il numero, sia premendo enter sia il tab la cella target perde lo stato attivo e lo prende la cella successiva.

[scossaPartecipante scossa Karma: 37 pts]

Aldo Ercolini ha scritto:

Perché una volta digitato il numero, sia premendo enter sia il tab la cella target perde lo stato attivo e lo prende la cella successiva.

Questo dipende dalle tue impostazioni di Excel quando premi invio (non spostare, sposta giù, etc.), e comunque lo spostamento scatena l'evento .SelectionChange e non l'evento .Change.

[Autore]

Articoli