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
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