Como editar los datos en un listbox

Editar los datos en un Listbox, cuando se encuentran filtrados. Modificar los datos, actualizar la hoja y actualizar el listbox. 


Código VBA:

------------------

Option Explicit
Dim a As Variant
Dim sh As Worksheet
Private Sub Actualizar_Click()
  Dim fila As Long
  
  If ListBox1.ListIndex = -1 Then
    MsgBox "Seleccionar un título"
    Exit Sub
  End If
  
  fila = ListBox1.List(ListBox1.ListIndex, 4)
  sh.Range("A" & fila).Value = TextBox2.Value
  sh.Range("D" & fila).Value = TextBox3.Value
  Call cargar_matriz
  Call FiltrarDatos
End Sub
Private Sub ListBox1_Click()
  TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 0)
  TextBox3.Value = ListBox1.List(ListBox1.ListIndex, 3)
  Label6.Caption = ListBox1.List(ListBox1.ListIndex, 4)
End Sub
Private Sub TextBox1_Change()
  Call FiltrarDatos
End Sub
Private Sub CmbAutor_Change()
  Call FiltrarDatos
End Sub
Private Sub CmbIdioma_Change()
  Call FiltrarDatos
End Sub
Sub FiltrarDatos()
  Dim b As Variant
  Dim i As Long, j As Long, k As Long
  Dim dic As Object
  
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  ListBox1.Clear
  
  For i = 1 To UBound(a)
    If LCase(a(i, 1)) Like "*" & LCase(TextBox1) & "*" And _
      a(i, 2) Like "*" & CmbAutor.Value And _
      a(i, 3) Like "*" & CmbIdioma.Value Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  ListBox1.List = b
End Sub
Private Sub UserForm_Activate()
Me.Top = Range("A10").Top
Me.Left = Range("B10").Left - 12
End Sub
Private Sub UserForm_Initialize()
  Dim c As Range
  Dim dic As Object
  
  Set sh = Sheets("Libros")
  Set dic = CreateObject("Scripting.Dictionary")
  
  For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
    dic(c.Value) = Empty
  Next
  CmbAutor.List = Application.Transpose(dic.keys)
  
  dic.RemoveAll
  For Each c In sh.Range("C2", sh.Range("C" & Rows.Count).End(3))
    dic(c.Value) = Empty
  Next
  CmbIdioma.List = Application.Transpose(dic.keys)
  
  Call cargar_matriz
End Sub
Sub cargar_matriz()
  Dim i As Long
  
  a = sh.Range("A2:E" & sh.Range("A" & Rows.Count).End(3).Row).Value
  For i = 1 To UBound(a)
    a(i, 5) = i + 1
  Next
  ListBox1.List = a
End Sub

----------------






No hay comentarios.:

Publicar un comentario