Convalida dati da elenco

Question

salve,

utilizzo spesso la convalida dati da elenco però se le voci sono tante purtroppo  la tendina non è facile da gestire  (ad es. non trova la voce con la lettera iniziale e devo scorrere tutte le voci singolarmente per trovare quella giusta).

Avete suggerimenti in merito e/o soluzioni alternative?

grazie

MF

Answer 1

Ciao MecFala. 

Ho aggiornato il tuo file come segue:

• Sul foglio Clienti, ho esteso l'elenco di clienti a 75 clienti e ho convertito l'elenco in una tabella di Excel, Tabella2
• Ho creato il nome ElencoClienti per la seconda colonna della tabella, cioe' i nomi delle ditte
• Ho ordinato la tabella in modo alfabetico
• Ho utilizzato questo nome, ElencoClienti, per alimentare la convalida dati sul foglio Giornale
• Nel modulo di codice del foglio Giornale ho incollato il seguente codice:

'=========>>

Option Explicit

'--------->>

Private Sub ComboBox1_KeyDown(ByVal _

                              KeyCode As MSForms.ReturnInteger, _

                              ByVal Shift As Integer)

    Select Case KeyCode

    Case 9  '\ Tab key

        ActiveCell.Offset(0, 1).Activate

    Case 13  '\ Enter key

        ActiveCell.Offset(1, 0).Activate

    Case Else

        '\ Do nothing

    End Select

End Sub

'--------->>

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim SHconvalida As Worksheet

    Dim sStr As String

    Dim cBox As MSForms.ComboBox

    Set cBox = Me.ComboBox1

    Set SHconvalida = ThisWorkbook.Sheets("Clienti")

    On Error GoTo XIT

    If Target.Validation.Type = 3 Then

        Cancel = True

    End If

    On Error Resume Next

    With cBox

        .ListFillRange = vbNullString

        .LinkedCell = vbNullString

        .Visible = False

    End With

    On Error GoTo XIT

    If Target.Validation.Type = 3 Then

        Application.EnableEvents = False

        sStr = Target.Validation.Formula1

        sStr = Right(sStr, Len(sStr) - 1)

        With cBox

            .Visible = True

            .Left = Target.Left

            .Top = Target.Top

            .Width = Target.Width + 15

            .Height = Target.Height + 5

            .ListFillRange = SHconvalida.Range(sStr).Address(External:=True)

            .LinkedCell = Target.Address

            .Activate

            .SelStart = 0

            .SelLength = Len(.Text)

            .DropDown

        End With

    End If

XIT:

    Application.EnableEvents = True

End Sub

'--------->>

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cBox As MSForms.ComboBox

    Dim sStr As String

    Set cBox = Me.ComboBox1

    On Error Resume Next

    With cBox    'Me.ComboBox1

        If .Visible = True Then

            .Top = 10

            .Left = 10

            .ListFillRange = ""

            .LinkedCell = ""

            .Visible = False

            .Value = ""

        End If

    End With

XIT:

    With Application

        .ScreenUpdating = True

        .EnableEvents = True

    End With

End Sub

'<<=========

In questo modo, si può utilizzare sia la normale convalida dei dati o, facendo doppio clic su una cella, utilizzare la ComboBox.

Con l'uso della ComboBox, digitando una o più lettere, viene visualizzato il nome corrispondente; se l'elenco di convalida dati sia ordinato in sequenza alfabetica, verrà selezionato il nome corrispondente e le 7 nomi seguenti verranno visualizzati al di sotto.

Ho caricato il file aggiornato MecFala3_20151122.xlsm a:

                        **http://1drv.ms/1NK5bKh**

===

Regards,

Norman

Answer 2

Ciao MecFala,

Nel caso il controllo ComboBox fosse eliminato dalla versione del file scaricato da OneDrive, come è successo a me quando ho provato a scaricarlo, per ricrearlo con VBA prova:

Alt+F11 per aprire l'editor di VBA

Alt+IM per inserire un nuovo modulo di codice

Nel nuovo modulo vuoto, incolla il seguente codice: 

'=========>>

Option Explicit

'--------->>

Public Sub AddComboBox()

    Dim OleObj As OLEObject

    Dim oComboBox As MSForms.ComboBox

    Const sElencoDati As String = "A2:A31"               '<<=== Modifica

    Set OleObj = ActiveSheet.OLEObjects.Add( _

                 ClassType:="Forms.ComboBox.1", _

                 Link:=False, _

                 DisplayAsIcon:=False, _

                 Left:=169.5, _

                 Top:=57.75, _

                 Width:=118.5, _

                 Height:=35.25)

    Set oComboBox = OleObj.Object

    With oComboBox

        .BackColor = &HC0FFFF

        .ForeColor = &HFF&

        .Font.Size = 14

        .FontBold = True

        .ListFillRange = sElencoDati

        .MatchEntry = fmMatchEntryComplete

    End With

End Sub

'<<========= 

• Alt+Q per chiudere l'editor di VBA e tornare a Excel.
• Alt+F8 per aprire  la finestra di gestione delle macro
• Seleziona AddComboBox
• Esegui

===

Regards,

Norman

Answer 3

Grazie di nuovo Norman!

A mio modo di vedere se la celle sono poche forse il  combobox può andare ma se si tratta di una tabella con tante righe  mettere sul foglio per ogni riga " dei controlli "galleggianti" non mi piace (forse mi sono spiegato male).

E' molto meno invasivo il menù a tendina della convalida dati.

Saluti e buona giornata!

MF

Answer 4

Ciao MecFala,

Credo tu possa sfruttare un controllo ComboBox dagli strumenti ActiveX

e imporre il valore fmMatchEntryComplete per la proprietà MatchEntry della ComboBox

Potresti scaricare il mio file di esempio MecFala20151122.xlsx a:

                          **http://1drv.ms/1QBxhxq**

===

Regards,

Norman