Proveedores y clientes
Public wsh As Worksheet
Public rng1 As Range
Private Sub txtcodigo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If frm_listarpersonas.txtpersona.Text = "Proveedores" Then
Set wsh = Worksheets("Hoja2")
Else
If frm_listarpersonas.txtpersona.Text = "Clientes" Then
Set wsh = Worksheets("Hoja3")
End If
End If
If KeyCode = 13 Then
If Me.txtopera.Text = "2" Then
Me.txtcodigo.Enabled = False
Me.cmdaceptar.Enabled = True
'Buscamos producto
Set rng1 = wsh.Range("A:A").Find(What:=Me.txtcodigo.Text, LookAt:=xlWhole, LookIn:=xlValues)
If rng1 Is Nothing Then
MsgBox "El producto no existe", vbOKOnly + vbInformation, "No encontrado"
Me.txtcodigo.Enabled = True
Else
Me.txtcodigo.Text = wsh.Range("A" & rng1.Row)
Me.txtnombre.Text = wsh.Range("B" & rng1.Row)
Me.txtdireccion.Text = wsh.Range("C" & rng1.Row)
Me.txttelefono.Text = wsh.Range("D" & rng1.Row)
Me.txtemail.Text = wsh.Range("E" & rng1.Row)
End If
End If
End If
End Sub
Private Sub cmdaceptar_Click()
Dim lngregistros As Long
Dim blnrespuesta As Boolean
If frm_listarpersonas.txtpersona.Text = "Proveedores" Then
Set wsh = Worksheets("Hoja2")
Else
If frm_listarpersonas.txtpersona.Text = "Clientes" Then
Set wsh = Worksheets("Hoja3")
End If
End If
'Validamos datos
If Me.txtnombre.Text = "" Then MsgBox "Nombre incorrecto", vbOKOnly + vbExclamation, "Nombre": Exit Sub
If Me.txtopera.Text = "1" Then
'Buscamos duplicado
Set rng1 = wsh.Range("A:A").Find(What:=Me.txtcodigo.Text, LookAt:=xlWhole, LookIn:=xlValues)
If rng1 Is Nothing Then
blnrespuesta = False
Else
blnrespuesta = True
End If
If blnrespuesta = True Then
MsgBox "La clave del producto ya existe", vbOKOnly + vbInformation, "Duplicado"
Me.txtcodigo.Enabled = True
Exit Sub
End If
lngregistros = wsh.Range("A1").CurrentRegion.Rows.Count
'Nos pregunta si deseamos guardar el registros
intrespuesta = MsgBox("¿Los datos son correctos?", vbYesNo + vbQuestion, "Antes de guardar")
'Si la respuesta es ya no continua ejecutándose la subrutina
If intrespuesta = vbNo Then
Exit Sub
Else
'Guardamos el registro
wsh.Cells(lngregistros + 1, 1) = lngregistros
wsh.Cells(lngregistros + 1, 2) = Me.txtnombre.Text
wsh.Cells(lngregistros + 1, 3) = Me.txtdireccion.Text
wsh.Cells(lngregistros + 1, 4) = Me.txttelefono.Text
wsh.Cells(lngregistros + 1, 5) = Me.txtemail.Text
MsgBox "El registro se guardó correctamente", vbOKOnly + vbInformation, "Guardado"
Me.txtopera.Text = "0"
Unload Me
End If
Else
If Me.txtopera.Text = "2" Then
'Modificamos el registro
'wsh.Range ("A" & rng1.Row)
wsh.Range("B" & rng1.Row) = Me.txtnombre.Text
wsh.Range("C" & rng1.Row) = Me.txtdireccion.Text
wsh.Range("D" & rng1.Row) = Me.txttelefono.Text
wsh.Range("E" & rng1.Row) = Me.txtemail.Text
MsgBox "El registro se modificó correctamente", vbOKOnly + vbInformation, "Modificó"
Me.txtopera.Text = "0"
Unload Me
End If
End If
End Sub
Búsqueda de proveedores y clientes por CODIGO o NOMBRE
Public wsh As Worksheet
Private Sub UserForm_Initialize()
EncabezadoListBox
End Sub
Private Sub EncabezadoListBox()
Dim intfila As Integer
'Definimos el número de columnas
Me.ListBox1.ColumnCount = 4
'Definimos el ancho de cada columna
Me.ListBox1.ColumnWidths = "50;250;250;80"
'Limpiar el lixtbox
ListBox1.Clear
'Agregar el elemento en las respecticas columnas
ListBox1.AddItem
intfila = ListBox1.ListCount - 1
ListBox1.Column(0, intfila) = "CODIGO"
ListBox1.Column(1, intfila) = "NOMBRE"
ListBox1.Column(2, intfila) = "DIRECCION"
ListBox1.Column(3, intfila) = "TELEFONO"
End Sub
Private Sub cmdnuevo_Click()
If frm_listarpersonas.txtpersona.Text = "Proveedores" Then
Set wsh = Worksheets("Hoja2")
frm_personas.txtcodigo.Text = wsh.Range("A1").CurrentRegion.Rows.Count
frm_personas.Caption = "Proveedores"
Else
If frm_listarpersonas.txtpersona.Text = "Clientes" Then
Set wsh = Worksheets("Hoja3")
frm_personas.txtcodigo.Text = wsh.Range("A1").CurrentRegion.Rows.Count
frm_personas.Caption = "Clientes"
End If
End If
frm_personas.txtopera.Text = "1"
frm_personas.cmdaceptar.Enabled = True
frm_personas.Show
End Sub
Private Sub txtcadena_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim intcont As Integer
Dim intfila As Integer
If Me.txtpersona.Text = "Proveedores" Then
Set wsh = Worksheets("Hoja2")
Else
If Me.txtpersona.Text = "Clientes" Then
Set wsh = Worksheets("Hoja3")
End If
End If
If KeyCode = 13 Then
intregistros = wsh.Range("A1").CurrentRegion.Rows.Count
EncabezadoListBox
For intcont = 2 To intregistros
If wsh.Cells(intcont, 1) Like "*" & Me.txtcadena.Value & "*" Then
Me.ListBox1.AddItem
intfila = Me.ListBox1.ListCount - 1
Me.ListBox1.Column(0, intfila) = wsh.Cells(intcont, 1)
Me.ListBox1.Column(1, intfila) = wsh.Cells(intcont, 2)
Me.ListBox1.Column(2, intfila) = wsh.Cells(intcont, 3)
Me.ListBox1.Column(3, intfila) = wsh.Cells(intcont, 4)
End If
If wsh.Cells(intcont, 2) Like "*" & Me.txtcadena.Value & "*" Then
Me.ListBox1.AddItem
intfila = Me.ListBox1.ListCount - 1
Me.ListBox1.Column(0, intfila) = wsh.Cells(intcont, 1)
Me.ListBox1.Column(1, intfila) = wsh.Cells(intcont, 2)
Me.ListBox1.Column(2, intfila) = wsh.Cells(intcont, 3)
Me.ListBox1.Column(3, intfila) = wsh.Cells(intcont, 4)
End If
Next intcont
End If
Me.lblregistros.Caption = "Registros econtrados: " & Me.ListBox1.ListCount - 1
End Sub
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If Me.ListBox1.ListIndex + 1 <= 1 Then Exit Sub
If KeyCode = 13 Then
frm_movimientos.txtcodigo.Text = Me.ListBox1.Column(0)
Unload Me
End If
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ListBox1.ListIndex + 1 > 1 Then
If Me.txtpersona.Text = "Proveedores" Then frm_personas.Caption = "Proveedores"
If Me.txtpersona.Text = "Clientes" Then frm_personas.Caption = "Clientes"
frm_personas.txtcodigo.Text = Me.ListBox1.Column(0)
frm_personas.txtopera.Text = "2"
frm_personas.Show
End If
End Sub
No hay comentarios.:
Publicar un comentario