Tengo un código de 3 combobox dependiente que no me funciona en todas las hojas
hola tengo el siguiente código de 3 combobox dependiente sin embargo no me funciona para abrir el formulario en todas las hojas el código es
'***Macro***
Public ufila As Integer
'***
Private Sub ComboBox1_Change()
ComboBox2.Clear
'Por.dam
código = 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)
ubicación = Cells(lnCount, 2)
If Cells(lnCount, 1) = código 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
código = ComboBox1.Value
ubicación = 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) = código And _
Cells(lnCount, 2) = ubicación 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
código = ComboBox1.Value
ubicación = ComboBox2.Value
fecha = ComboBox3.Value
For i = 1 To ufila
cantidad = Cells(i, 4)
If Cells(i, 1) = código And _
Cells(i, 2) = ubicación 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***