Help with code issues

M

Mekinnik

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(external:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(external:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(external:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(external:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(external:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(external:=True)

End Sub
 
J

JLGWhiz

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)
 
M

Mekinnik

I was just testing it to see if it would work if one sheet had more data in it.
 
J

JLGWhiz

You said this worked in testing. Did you have the initialize code using two
worksheet references and non-contiguous range references for RowSource when
you were testing? I have never seen that done before. Exactly where do you
get the error message when you try to run the code?
 
M

Mekinnik

I get an unspecified error code @ the .clear, in addition its really not
giving me an error it just that it keeps showing in cbxprod the same names as
in cbxman when it is suppose to show the names that match. this is what each
sheet looks like:

Sheet(MANCODE)
manufacturer name address ect, ect.
name 123 name
mane ******
so on and so fourth


Sheet(ProCode)
manufacturer name Product name
name green
mane blue
same red
name red
mane green
name blue

Its suppose to show for each say "name"(cbxman) in column 'A' show in
cbxprod.list (green,red,blue).
Which like I said it does in the test version, however in the production
version it keeps just showing whatever is in the cbxman list.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top