Probably irrelevant to your problem, but I was curious about the difference
in the range references in these two lines. Since you are essentially doing
the same thing from different approaches, it seemed odd that one range has
seven cells more in one test than it does in the second test.

V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
---
For Each R In Worksheets("test").Range("A1:A17")
---

"Mekinnik" wrote:

Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub