How to make this work??

[Mekinnik]

How can I make this work for 2 comboboxes an the same userform and make it
that is the value from combox('CbxMan').value doesn't match anything in
column 'A' or a new name is typed in it will open a new userform? Here is my
current code.

Private Sub CbxMfg_Change()
Dim sSelected As String
Dim dX As Double, dCount As Double
sSelected = Me.CbxMfg.Value
dCount = 0
For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count
If Cells(dX, 1).Value = sSelected Then
Me.CbxProd.AddItem Cells(d, 2).Value
dCount = dCount + 1
End If
Next
If Not dCount = 0 Then
FrmManu.Show
Unload Me
End If
End Sub


AND

Private Sub UserForm_Initialize()
Dim coll As Collection
Dim i As Long
Dim itm
'fill coll with values from column A
Set coll = New Collection
On Error Resume Next
For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count
coll.Add Range("A" & i).Value, Range("A" & i).Value
Next i
On Error GoTo 0
For Each itm In coll
Me.CbxMfg.AddItem itm
Next

End Sub

 

[Bob Phillips]

Private Sub CbxMfg_Change()
Dim sSelected As String
Dim dX As Double, dCount As Double
sSelected = Me.CbxMfg.Value
If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0))
Then
Me.Hide
FrmManu.Show
End If
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mekinnik]

Mr. Phillips,
How could I catch the error in the following code and allow the user to
continue on to enter the information. Right now my database is empty and I am
designing this as I go? I keep getting an error at the ** mark due to the
fact that the columns are empty.

Private Sub CbxMfg_Change()
Dim sSelected As String
Dim dX As Double, dCount As Double
bEnablEvents = False
sSelected = Me.CbxMfg.Value
dCount = 0
If Not dCount = 0 Then
FrmManu.Show
FrmProduct.Hide
End If
For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count
If Cells(dX, 1).Value = sSelected Then
** Me.CbxProd.AddItem Cells(dX, 2).Value
dCount = dCount + 1
End If
Next

End Sub

 

[Bob Phillips]

Don't do it that way, I showed you a better way.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mekinnik]

I've been trying it your way and I keep getting an error, I've tried to
manipulate the line of code all different ways and it just is not working

 

[Mekinnik]

Mr. Phillips,
I am having a heck of a time getting this thing to work properly, I fix one
error and another happens. I know we like to keep thing in the forum but I am
not good at explaining what it is that I'm trying to do and without posting a
maddive post with all my code, I would like to know if I could e-mail you my
excel file for you to take a lokk at, if not thanks anyway and I will just
keep plugging away at it one error at a time?

 

[Bob Phillips]

I only have tomorrow available, but you could send it to me

bob dot phillips at tiscali dot co dot uk

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mekinnik]

Mr. phillips,
I will send it to you tomorrow morning, I have it at work.