Scribd
Upload
50 views9 pages

Excel VBA Inventory Management

The document contains code for a VBA userform that manages an inventory list. It initializes the form with list properties, allows searching the list and editing/deleting items. It also includes code to add/remove items and track item entries and exits.

Uploaded by

Karla Rocío Lara de santos
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
50 views9 pages

Excel VBA Inventory Management

The document contains code for a VBA userform that manages an inventory list. It initializes the form with list properties, allows searching the list and editing/deleting items. It also includes code to add/remove items and track item entries and exits.

Uploaded by

Karla Rocío Lara de santos
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Private Sub UserForm_Initialize()

Me.Lista.RowSource = "inventario"

Me.Lista.ColumnCount = 8

Me.Lista.ColumnHeads = True

Me.Lista.ColumnWidths = "100;200;50;50;50;50;50;50"

End Sub

Private Sub BT_Buscar_Click()

Dim ULFILA As Variant

Dim FILA As Variant

Me.Lista.RowSource = Clear

ULFILA = Sheets("INVENTARIO").Range("A" & Rows.Count).End(xlUp).Row

For FILA = 3 To ULFILA

If Range("A" & FILA) Like Me.TextBuscar.Value Then

Me.Lista.AddItem Cells(FILA, 1)

Me.Lista.List(Lista.ListCount - 1, 0) = Cells(FILA, 1)

Me.Lista.List(Lista.ListCount - 1, 1) = Cells(FILA, 2)

Me.Lista.List(Lista.ListCount - 1, 2) = Cells(FILA, 3)

Me.Lista.List(Lista.ListCount - 1, 3) = Cells(FILA, 4)

Me.Lista.List(Lista.ListCount - 1, 4) = Cells(FILA, 5)
Me.Lista.List(Lista.ListCount - 1, 5) = Cells(FILA, 6)

Me.Lista.List(Lista.ListCount - 1, 6) = Cells(FILA, 7)

Me.Lista.List(Lista.ListCount - 1, 7) = Cells(FILA, 8)

End If

Next FILA

End Sub

Private Sub BT_Editar_Click()

Dim FILA As Object

Dim LINEA As Variant

Dim ValorBuscado As Variant

If Me.Lista.ListIndex = -1 Then

MsgBox "SELECCINE EL PRODUCTO A EDITAR"

Else

Me.TextCodProd = Me.Lista

ValorBuscado = TextCodProd

Set FILA = Sheets("INVENTARIO").Range("A:A").Find(ValorBuscado, lookat:=xlWhole)

LINEA = FILA.Row

MsgBox (LINEA)

Me.TextDescripcion.Value = Range("B" & LINEA).Value

Me.TextLote.Value = Range("C" & LINEA).Value

Me.TextCostUni.Value = Range("G" & LINEA).Value


Me.TextDescripcion.Value = Range("B" & LINEA).Value

Me.TextDescripcion.Value = Range("B" & LINEA).Value

Me.TextDescripcion.Value = Range("B" & LINEA).Value

Me.TextDescripcion.Value = Range("B" & LINEA).Value

Me.TextDescripcion.Value = Range("B" & LINEA).Value

End If

End Sub

Private Sub BT_Eliminar_Click()

Dim FILA As Object

Dim LINEA As Variant

Dim CodigoBuscado As Variant

If Me.Lista.ListIndex = -1 Then

MsgBox "SELECCIONE EL DATO A ELIMINAR"

Else

Me.TextCodProd.Value = Me.Lista

CodigoBuscado = Me.TextCodProd

Set FILA = Sheets("INVENTARIO").Range("A:A").Find(CodigoBuscado, lookat:=xlWhole)


LINEA = FILA.Row

Range("A" & LINEA).EntireRow.Delete

End If

MsgBox "EL PRODUCTO FUE ELIMINADO"

Me.Lista.RowSource = "inventario"

Me.Lista.ColumnCount = 8

Me.TextCodProd = Empty

End Sub

Private Sub BT_NvoCodigo_Click()

Sheets("INVENTARIO").Range("A4").EntireRow.Insert , COPYORIGIN:=xlFormatFromRightOrBelow

Range("A4").Value = Me.TextCodProd.Value

Range("B4").Value = Me.TextDescripcion.Value

Range("C4").Value = Me.TextLote.Value

Range("G4").Value = Me.TextCostUni.Value

Me.Lista.RowSource = "inventario"

Me.Lista.ColumnCount = 8

End Sub
Private Sub BT_Entrada_Click()

Sheets("ENTRADAS").Range("A4").EntireRow.Insert , COPYORIGIN:=xlFormatFromRightOrBelow

Range("A4").Value = Me.TextDocEnt.Value

Range("B4").Value = Me.TextFecha.Value

Range("C4").Value = Me.TextCodProd.Value

Range("D4").Value = Me.TextDescripcion.Value

Range("E4").Value = Me.TextLote.Value

Range("F4").Value = Me.TextCantidad.Value

Me.TextCodProd = Empty

Me.TextDescripcion = Empty

Me.TextLote = Empty

Me.TextCostUni = Empty

Me.TextDocEnt = Empty

Me.TextDocSal = Empty

Me.TextFecha = Empty

Me.TextCantidad = Empty

End Sub

Private Sub BT_Salida_Click()

Sheets("SALIDAS").Range("A4").EntireRow.Insert , COPYORIGIN:=xlFormatFromRightOrBelow

Range("A4").Value = Me.TextDocSal.Value

Range("B4").Value = Me.TextFecha.Value
Range("C4").Value = Me.TextCodProd.Value

Range("D4").Value = Me.TextDescripcion.Value

Range("E4").Value = Me.TextLote.Value

Range("F4").Value = Me.TextCantidad.Value

Me.TextCodProd = Empty

Me.TextDescripcion = Empty

Me.TextLote = Empty

Me.TextCostUni = Empty

Me.TextDocEnt = Empty

Me.TextDocSal = Empty

Me.TextFecha = Empty

Me.TextCantidad = Empty

End Sub

Private Sub BT_MostrarCampos_Click()

Me.Height = 426

End Sub

Private Sub BT_OcultarCampos_Click()

Me.Height = 261

End Sub
Private Sub BT_EditarProducto_Click()

Dim FILA As Object

Dim LINEA As Variant

Dim ValorBuscado As Variant

Me.TextCodProd = Me.Lista

ValorBuscado = TextCodProd

Set FILA = Sheets("INVENTARIO").Range("A:A").Find(ValorBuscado, lookat:=xlWhole)

LINEA = FILA.Row

Range("B" & LINEA).Value = Me.TextDescripcion.Value

Range("C" & LINEA).Value = Me.TextLote.Value

Range("G" & LINEA).Value = Me.TextCostUni.Value

Me.TextCodpROD = Empty

Me.TextDescripcion = Empty

Me.TextLote = Empty

Me.TextCostUni = Empty

MsgBox ("EL PRODUCTO FUE MODIFICADO CON EXITO")

Me.Lista.RowSource = "inventario"

Me.Lista.ColumnCount = 8

Range("B" & LINEA).Value = Me.TextDescripcion.Value

Range("B" & LINEA).Value = Me.TextDescripcion.Value


Range("B" & LINEA).Value = Me.TextDescripcion.Value

Range("B" & LINEA).Value = Me.TextDescripcion.Value

Range("B" & LINEA).Value = Me.TextDescripcion.Value

Range("B" & LINEA).Value = Me.TextDescripcion.Value

End Sub

Private Sub BT_MostrarExcel_Click()

Application.Visible = True

Unload Me

End Sub
Sub MOSTRAR_FORMULARIO()

UserForm1.Show

End Sub

Sub OCULTAR_EXCEL()

Application.Visible = False

UserForm1.Show

End Sub

You might also like