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