Userform combobox dependiente

Tengo una hoja columna A, B, C, D, con títulos, necesito que al

Código ubicación fecha cantidad
BFDAC12 RACK1 10-10-2012 1

FGTED52 RACK2 11-10-2012 2

DFREW34 RACK3 12-10-2012 3

BFDAC12 RACK4 13-10-2012 4

DFREW34 RACK3 14-10-2012 1

FDAC12 RACK4 13-10-2012 2

DFREW34 RACK2 16-10-2012 3

EWRS67 RACK3 17-10-2012 4

BFDAC12 RACK1 18-10-2012 1

EWRS67 RACK3 19-10-2012 2

BFDAC12 RACK4 20-10-2012 3

EWRS67 RACK1 21-10-2012 4

BFDAC12 RACK1 22-10-2012 1

FGTED52 RACK5 23-10-2012 3

EWRS67 RACK3 24-10-2012 3

Los datos están en una sola hoja.

La idea es que si elijo en el COMBOBOX1 BFDAC12 en el COMBOBOX2 "ubicación" solo me muestre el Rack1 y rack4, una vez elegido Rack4 en el COMBOBOX3 "Fechas" solo me muestre las fechas

13-10-2012, 13-10-2012, 20-10-2012 y por ultimo si elijo la fecha 13-10-2012 solo me muestre en el COMBOBOX4 el stock 4 y 2, te comento esto porque tengo una macro de entrada y salida de producto, solamente cantidades y me gustaría incorporarle esta herramienta.

Todo esto esta dentro de un userform.

Atento estaré de tu colaboración, un abrazo, Eduardo

Dim UltimaFila As Double
Private Sub C1_Click()
C2.Clear C3.Clear C4.Clear
For x = 2 To UltimaFila If ActiveSheet.Cells(x, 1) = C1.Text Then C2.AddItem ActiveSheet.Cells(x, 2) C3.AddItem ActiveSheet.Cells(x, 3) C4.AddItem ActiveSheet.Cells(x, 4) End If
Next x
End Sub
Private Sub C3_Click() C4.ListIndex = C3.ListIndex End Sub Private Sub SALIR_Click() Unload Me End Sub
Private Sub UserForm_Initialize()
ThisWorkbook.Worksheets("Hoja1").Activate UltimaFila = ActiveSheet.Cells(65536, 1).End(xlUp).Row
For x = 2 To UltimaFila C1.Text = ActiveSheet.Cells(x, 1) If C1.ListIndex = -1 Then C1.AddItem ActiveSheet.Cells(x, 1) Next x C2 = ActiveSheet.Cells(x, 2)
C1.Text = "" C2.Clear C3.Clear C4.Clear
End Sub

1 Respuesta

Respuesta
3

Te anexo la macro, deberás tener lo siguiente en tu userform
ComboBox1, ComboBox2, ComboBox3 y ComboBox4,
Tu hoja se debe llamar “Hoja1”
Si la selección entre un combo y otro es lenta, es porque está haciendo el filtro para obtener los valores únicos; así está tu solicitud.

'***Macro***
Public ufila As Integer
'***
Private Sub ComboBox1_Change()
    ComboBox2.Clear
    'Por.dam
    codigo = ComboBox1.Value
    Dim rnData As Range
    Dim vaData As Variant               'the list, stored in a variant
    Dim ncData As New VBA.Collection    'the list, stored in a collection
    Dim lnCount As Long                 'the count used in the On Error Resume Next loop.
    Dim vaItem As Variant               'a variant representing the type of items in ncData
    'Using ,retrieve the range of the list in Column b.
    With ThisWorkbook.Worksheets("Hoja1")
        Set rnData = .Range(.Range("B1"), .Range("B" & Rows.Count).End(xlUp))
    End With
    'Place the list values into vaData.
    vaData = rnData.Value
    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
            ubicacion = Cells(lnCount, 2)
            If Cells(lnCount, 1) = codigo Then
                ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
            End If
        Next lnCount
    On Error GoTo 0
    With Me.ComboBox2
        .Clear
        For Each vaItem In ncData
            .AddItem ncData(vaItem)
        Next vaItem
    End With
End Sub
'***
Private Sub ComboBox2_Change()
    'Por.dam
    ComboBox3.Clear
    codigo = ComboBox1.Value
    ubicacion = ComboBox2.Value
    Dim rnData As Range
    Dim vaData As Variant               'the list, stored in a variant
    Dim ncData As New VBA.Collection    'the list, stored in a collection
    Dim lnCount As Long                 'the count used in the On Error Resume Next loop.
    Dim vaItem As Variant               'a variant representing the type of items in ncData
    'Using ,retrieve the range of the list in Column b.
    With ThisWorkbook.Worksheets("Hoja1")
        Set rnData = .Range(.Range("C1"), .Range("C" & Rows.Count).End(xlUp))
    End With
    'Place the list values into vaData.
    vaData = rnData.Value
    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
            fecha = Cells(lnCount, 3)
            If Cells(lnCount, 1) = codigo And _
                Cells(lnCount, 2) = ubicacion Then
                ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
            End If
        Next lnCount
    On Error GoTo 0
    With Me.ComboBox3
        .Clear
        For Each vaItem In ncData
            .AddItem ncData(CStr(vaItem))
        Next vaItem
    End With
End Sub
'***
Private Sub ComboBox3_Change()
    'Por.dam
    ComboBox4.Clear
    codigo = ComboBox1.Value
    ubicacion = ComboBox2.Value
    fecha = ComboBox3.Value
    For i = 1 To ufila
        cantidad = Cells(i, 4)
        If Cells(i, 1) = codigo And _
            Cells(i, 2) = ubicacion And _
            Val(Cells(i, 3)) = Val(fecha) Then
            With Me.ComboBox4
                .AddItem cantidad
            End With
        End If
   Next
End Sub
'***
Private Sub UserForm_Activate()
Dim strRango As String
Dim i As Integer
    'Por.dam
    'The Excel workbook and worksheets that contain the data, as well as the range placed on that data
    Dim rnData As Range
    Dim vaData As Variant               'the list, stored in a variant
    Dim ncData As New VBA.Collection    'the list, stored in a collection
    Dim lnCount As Long                 'the count used in the On Error Resume Next loop.
    Dim vaItem As Variant               'a variant representing the type of items in ncData
    'Instantiate the Excel objects.
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Hoja1")
        ufila = Range("A" & Rows.Count).End(xlUp).Row
    'Using Sheet2,retrieve the range of the list in Column A.
    'With wsSheet
    With ThisWorkbook.Worksheets("Hoja1")
        'Set rnData = .Range(.Range("A2"), .Range("A100").End(xlUp))
        Set rnData = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    End With
    'Place the list values into vaData.
    vaData = rnData.Value
    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
            ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
        Next lnCount
    On Error GoTo 0
    'and then add each unique variant item from ncData to the combo box.
    With Me.ComboBox1
        .Clear
        For Each vaItem In ncData
            .AddItem ncData(vaItem)
        Next vaItem
    End With
End Sub
'***Macro***

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas