Sorry if i'm not understanding, but this would be an example:
Code: Select all
Private Sub ComboBoxIBAN_Change()
On Error GoTo ExceptionHandling
Application.EnableEvents = False
Dim IBAN As String, IdAr() As Variant, i As Integer
IBAN = Me.ComboBoxIBAN.value
IdAr = Application.Transpose(Sheets("DATOS").ListObjects("TB_IBAN").ListColumns("IBAN").DataBodyRange)
For i = 1 To UBound(IdAr)
If IdAr(i) = IBAN Then
With Sheets("DATOS").ListObjects("TB_IBAN").ListColumns("Elegir_M303")
.DataBodyRange.ClearContents
.DataBodyRange(i) = 1
End With
End If
Next i
CleanUp:
On Error Resume Next
Application.EnableEvents = True
Exit Sub
ExceptionHandling:
MsgBox "Error: " & Err.Description
Resume CleanUp
Resume 'for debugging
End Sub
Private Sub ComboBoxIBAN_DropButtonClick()
If Me.ComboBoxIBAN.ListCount > 0 Then Exit Sub
'populate ComboBox
Dim IdAr() As Variant
IdAr = Application.Transpose(Sheets("DATOS").ListObjects("TB_IBAN").ListColumns("IBAN").DataBodyRange)
Me.ComboBoxIBAN.List = IdAr
End Sub
Or using LostFocus event with the Value property (sorry for not having time now to write a general example):
Code: Select all
Private Sub ComboBox1_LostFocus()
On Error GoTo ExceptionHandling
Application.EnableEvents = False
With Me
.Range("A11").Value = .ComboBox1.Value
End With
CleanUp:
On Error Resume Next
Application.EnableEvents = True
Exit Sub
ExceptionHandling:
'MsgBox "Error: " & Err.Description
Resume CleanUp
Resume 'for debugging
End Sub
Private Sub ComboBox1_GotFocus()
Me.ComboBox1.Value = ""
'ComboBox1.SelStart = 0
'ComboBox1.SelLength = Len(ComboBox1.text)
'CreateObject("WScript.Shell").SendKeys "{DELETE}", True
End Sub
Private Sub ComboBox1_DropButtonClick()
'http://stackoverflow.com/questions/31660393/excel-combo-box-refresh-dropdown
'Getting the list to ONLY show values that matched the text typed by the user ...
'Note that the MacthEntry Property of the combo box MUST be set to "2 - frmMatchEntryNone" for the code to work.
'(Other values cause the combo box .value property store the text of the first value that matches what the user typed, and the code relies on it storing what they typed.)
'Also, the code will pick up any items on the list that have the letters typed by the user ANYWHERE in their text.
''refresh the list
''Dim ValidValues() As Variant
''ValidValues = Worksheets("CLIENTES").Range("Client_TB[Nombre]").Value
''Me.ComboBox_SiteName.List = ValidValues
'populate combobox
'Antes usaba a propiedade ListFillRange, pero había que pechar arquivo para actualizar listas
Dim tbl As ListObject, ESP As Boolean
Set tbl = Sheets("CLIENTES").ListObjects("Client_TB")
Dim IdArr As Variant, IdArr2 As Variant
IdArr = Application.Transpose(tbl.ListColumns("Nombre").DataBodyRange)
IdArr2 = Application.Transpose(tbl.ListColumns("IVA").DataBodyRange)
If Me.OptionButtonESP.Value = True Then ESP = True
Dim y As Long, i As Integer, myCount As Integer
y = UBound(IdArr)
If ESP = True Then
ReDim a_Es(1 To y)
For i = 1 To y
If IdArr2(i) = 1 Then
myCount = myCount + 1
a_Es(myCount) = IdArr(i)
End If
Next
ReDim Preserve a_Es(1 To myCount)
'Me.OLEObjects("Combobox1").Object.List = a_Es
With Me.ComboBox1
.List = Filter(a_Es, .Value, True, vbTextCompare)
End With
Else
ReDim a_Pt(1 To y)
For i = 1 To y
If IdArr2(i) = 0 Then
myCount = myCount + 1
a_Pt(myCount) = IdArr(i)
End If
Next
ReDim Preserve a_Pt(1 To myCount)
'Me.OLEObjects("Combobox1").Object.List = a_Es
With Me.ComboBox1
.List = Filter(a_Pt, .Value, True, vbTextCompare)
End With
End If
End Sub