Re: ComboBox Row Source across ALL sheets

[Corey]

Norman, thanks for your assistance.

I have got the 2 ComboBox lists populated now.
One for the Customer name and
the 2nd one for the Conveyor Name.
Both have values from the WorkSheet cells now.

However, is there a way to CONDITION the 2nd ComboBox to ONLY populate with
RELATED Conveyors ?
EG.
If Customer FRED has a Conveyor CV1
& Customer BARNEY has a Conveyor CV5,

Currently when i Pick say FRED from the 1st ComboBox, the Conveyor ComboBox
displays CV1 & CV5. Where CV5 is NOT a Conveyor that belongs to FRED.


Regards
Corey....

[crazybass2]

Corey,

From what I've read of your past posts, I think this is what you're looking
for. You need to have a crossreference for customer/conveyor number. Since
I have found no reference to it in your previous posts, I've assumed that
each sheet is for a specific customer and has a list of the conveyor numbers
for that customer. In the code, customer names are in Combobox1, converyors
are in Combobox2. You will need to change these references to match yours.
Each time Combobox1 changes Combobox2 will be cleared a repopulated with the
appropriate converyor numbers. You will need to write the code to replace
the "Set cvs = wks.range("A2:A5")" line to fit the location of the converyor
numbers.

Add this code to your Userform module:

Private Sub ComboBox1_Change()
Dim cvs As Range
ComboBox2.Clear
For Each wks In Worksheets
If wks.Range("A1").Text = ComboBox1.Value Then
Set cvs = wks.Range("A2:A5") 'CHANGE THIS LINE TO REFERENCE YOUR CVs
For Each cv In cvs
ComboBox2.AddItem cv.Text
Next cv
End If
Next wks
End Sub

If this is not how your sheets are setup, please reply and detail how the
customer/conveyor numbers are cross-referenced.

Mike

"Corey" wrote:

>
> Norman, thanks for your assistance.
>
> I have got the 2 ComboBox lists populated now.
> One for the Customer name and
> the 2nd one for the Conveyor Name.
> Both have values from the WorkSheet cells now.
>
> However, is there a way to CONDITION the 2nd ComboBox to ONLY populate with
> RELATED Conveyors ?
> EG.
> If Customer FRED has a Conveyor CV1
> & Customer BARNEY has a Conveyor CV5,
>
> Currently when i Pick say FRED from the 1st ComboBox, the Conveyor ComboBox
> displays CV1 & CV5. Where CV5 is NOT a Conveyor that belongs to FRED.
>
>
> Regards
> Corey....
>
>
>

[Corey]

Mike,
Thanks for the time to reply to my post.
To Date i remain at a stalemate, with not being any closer to a solution.

I am able to populate the combobox3 witht he Customer names "B3"
and populate the combobox4 with the Conveyor Name "D3"

You are on the right track with your comments, but I have 1 Customer Name
per Sheet, and Either 0 or 1 Conveyor Name per sheet.

What i am trying to do is carry out a FIND, based on the Customer AND
Conveyor, to FIND all sheets that MATCH those selected ComboBox Choices.

I need a Conveyor Condition of the Customer Name due to the fact that Some
Customers have a Conveyor Name the same. (EG. CV1, CV....)

When i replaced what i had with your code i did not get any values in the
Comboboxes at all??

If i have 3 Sheets with B3="FRED" & D3="CV1"
and
5 Sheets with B3="BARNEY" & D3="CV1"
and
4 Sheets with B3="FRED" & D3="CV2"

Then i want a FIND of say:

ComboBox3="FRED" & ComboBox4="CV2" <====== Want ONLY FRED's Conveyors to be
Displayed in ComboBox4, when ComboBox 3 has FRED as the Selection.


I want a LIST given of the MATCHING Sheets, AS is the case with a MANUAL
FIND does.

Any assistance is appreciated.

Regards

Corey

"crazybass2" <crazybass2@discussions.microsoft.com> wrote in message
news:71D4087D-51C6-438D-92DA-88047AF6A999@microsoft.com...
> Corey,
>
> From what I've read of your past posts, I think this is what you're
> looking
> for. You need to have a crossreference for customer/conveyor number.
> Since
> I have found no reference to it in your previous posts, I've assumed that
> each sheet is for a specific customer and has a list of the conveyor
> numbers
> for that customer. In the code, customer names are in Combobox1,
> converyors
> are in Combobox2. You will need to change these references to match
> yours.
> Each time Combobox1 changes Combobox2 will be cleared a repopulated with
> the
> appropriate converyor numbers. You will need to write the code to replace
> the "Set cvs = wks.range("A2:A5")" line to fit the location of the
> converyor
> numbers.
>
> Add this code to your Userform module:
>
> Private Sub ComboBox1_Change()
> Dim cvs As Range
> ComboBox2.Clear
> For Each wks In Worksheets
> If wks.Range("A1").Text = ComboBox1.Value Then
> Set cvs = wks.Range("A2:A5") 'CHANGE THIS LINE TO REFERENCE YOUR CVs
> For Each cv In cvs
> ComboBox2.AddItem cv.Text
> Next cv
> End If
> Next wks
> End Sub
>
> If this is not how your sheets are setup, please reply and detail how the
> customer/conveyor numbers are cross-referenced.
>
> Mike
>
> "Corey" wrote:
>
>>
>> Norman, thanks for your assistance.
>>
>> I have got the 2 ComboBox lists populated now.
>> One for the Customer name and
>> the 2nd one for the Conveyor Name.
>> Both have values from the WorkSheet cells now.
>>
>> However, is there a way to CONDITION the 2nd ComboBox to ONLY populate
>> with
>> RELATED Conveyors ?
>> EG.
>> If Customer FRED has a Conveyor CV1
>> & Customer BARNEY has a Conveyor CV5,
>>
>> Currently when i Pick say FRED from the 1st ComboBox, the Conveyor
>> ComboBox
>> displays CV1 & CV5. Where CV5 is NOT a Conveyor that belongs to FRED.
>>
>>
>> Regards
>> Corey....
>>
>>
>>

[crazybass2]

Corey,

I think this will do it. I've give the LIST in a msgbox, but you can modify
the code for your desires....let me know if you have any questions.

Mike


Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
Private Sub UserForm_Activate()
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False
Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub
Private Sub ComboBox3_Change()
ComboBox4.Clear
For Each wks In Worksheets
If wks.Range("b3").Text = ComboBox3.Value Then
addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
Next j
If addit2 Then ComboBox4.AddItem wks.Range("D3").Text
End If
Next wks
End Sub
Private Sub Combobox4_Change()
Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
Next wks
MsgBox (combolist)
End Sub
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub



"Corey" wrote:

> Mike,
> Thanks for the time to reply to my post.
> To Date i remain at a stalemate, with not being any closer to a solution.
>
> I am able to populate the combobox3 witht he Customer names "B3"
> and populate the combobox4 with the Conveyor Name "D3"
>
> You are on the right track with your comments, but I have 1 Customer Name
> per Sheet, and Either 0 or 1 Conveyor Name per sheet.
>
> What i am trying to do is carry out a FIND, based on the Customer AND
> Conveyor, to FIND all sheets that MATCH those selected ComboBox Choices.
>
> I need a Conveyor Condition of the Customer Name due to the fact that Some
> Customers have a Conveyor Name the same. (EG. CV1, CV....)
>
> When i replaced what i had with your code i did not get any values in the
> Comboboxes at all??
>
> If i have 3 Sheets with B3="FRED" & D3="CV1"
> and
> 5 Sheets with B3="BARNEY" & D3="CV1"
> and
> 4 Sheets with B3="FRED" & D3="CV2"
>
> Then i want a FIND of say:
>
> ComboBox3="FRED" & ComboBox4="CV2" <====== Want ONLY FRED's Conveyors to be
> Displayed in ComboBox4, when ComboBox 3 has FRED as the Selection.
>
>
> I want a LIST given of the MATCHING Sheets, AS is the case with a MANUAL
> FIND does.
>
> Any assistance is appreciated.
>
> Regards
>
> Corey
>
> "crazybass2" <crazybass2@discussions.microsoft.com> wrote in message
> news:71D4087D-51C6-438D-92DA-88047AF6A999@microsoft.com...
> > Corey,
> >
> > From what I've read of your past posts, I think this is what you're
> > looking
> > for. You need to have a crossreference for customer/conveyor number.
> > Since
> > I have found no reference to it in your previous posts, I've assumed that
> > each sheet is for a specific customer and has a list of the conveyor
> > numbers
> > for that customer. In the code, customer names are in Combobox1,
> > converyors
> > are in Combobox2. You will need to change these references to match
> > yours.
> > Each time Combobox1 changes Combobox2 will be cleared a repopulated with
> > the
> > appropriate converyor numbers. You will need to write the code to replace
> > the "Set cvs = wks.range("A2:A5")" line to fit the location of the
> > converyor
> > numbers.
> >
> > Add this code to your Userform module:
> >
> > Private Sub ComboBox1_Change()
> > Dim cvs As Range
> > ComboBox2.Clear
> > For Each wks In Worksheets
> > If wks.Range("A1").Text = ComboBox1.Value Then
> > Set cvs = wks.Range("A2:A5") 'CHANGE THIS LINE TO REFERENCE YOUR CVs
> > For Each cv In cvs
> > ComboBox2.AddItem cv.Text
> > Next cv
> > End If
> > Next wks
> > End Sub
> >
> > If this is not how your sheets are setup, please reply and detail how the
> > customer/conveyor numbers are cross-referenced.
> >
> > Mike
> >
> > "Corey" wrote:
> >
> >>
> >> Norman, thanks for your assistance.
> >>
> >> I have got the 2 ComboBox lists populated now.
> >> One for the Customer name and
> >> the 2nd one for the Conveyor Name.
> >> Both have values from the WorkSheet cells now.
> >>
> >> However, is there a way to CONDITION the 2nd ComboBox to ONLY populate
> >> with
> >> RELATED Conveyors ?
> >> EG.
> >> If Customer FRED has a Conveyor CV1
> >> & Customer BARNEY has a Conveyor CV5,
> >>
> >> Currently when i Pick say FRED from the 1st ComboBox, the Conveyor
> >> ComboBox
> >> displays CV1 & CV5. Where CV5 is NOT a Conveyor that belongs to FRED.
> >>
> >>
> >> Regards
> >> Corey....
> >>
> >>
> >>
>
>
>

[Corey]

Mike,
Thanks again for the time to reply.
It works SPOT ON, exactly as i need.
It narrows down the 2nd ComboBox to display ONLY values that are on the
worksheet that contain a value in the 1st ComboBox.
The only thing i need to work on now is to have the ComboBox Value FOUND
across all sheets and a list of sheets displayed.
I will place this in a New post.

Thanks Again Mike,
you are a Champion.

Corey....

Private Sub UserForm_Activate()
Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub

Private Sub ComboBox3_Change() ComboBox4.Clear For Each wks In Worksheets
If wks.Range("b3").Text = ComboBox3.Value Then addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
Next j
If addit2 Then ComboBox4.AddItem wks.Range("D3").Text End If
Next wks
End Sub

Private Sub Combobox4_Change() Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
Next wks
' MsgBox (combolist)
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

[crazybass2]

Corey,

The code I sent you should give a message box after you make a selection in
Combobox4 (converyor). Are you looking for the same format as the Excel Find
All?

Mike

"Corey" wrote:

>
> Mike,
> Thanks again for the time to reply.
> It works SPOT ON, exactly as i need.
> It narrows down the 2nd ComboBox to display ONLY values that are on the
> worksheet that contain a value in the 1st ComboBox.
> The only thing i need to work on now is to have the ComboBox Value FOUND
> across all sheets and a list of sheets displayed.
> I will place this in a New post.
>
> Thanks Again Mike,
> you are a Champion.
>
> Corey....
>
> Private Sub UserForm_Activate()
> Dim i As Integer, j As Integer
> Dim addit As Boolean, addit2 As Boolean
> ComboBox3.Clear
> For Each wks In Worksheets
> addit = True
> For i = 0 To ComboBox3.ListCount - 1
> If ComboBox3.ListCount = 0 Then Exit For
> If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False Next i
> If addit Then ComboBox3.AddItem wks.Range("B3").Text
> Next wks
> End Sub
>
> Private Sub ComboBox3_Change() ComboBox4.Clear For Each wks In Worksheets
> If wks.Range("b3").Text = ComboBox3.Value Then addit2 = True
> For j = 0 To ComboBox4.ListCount - 1
> If ComboBox4.ListCount = 0 Then Exit For
> If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
> Next j
> If addit2 Then ComboBox4.AddItem wks.Range("D3").Text End If
> Next wks
> End Sub
>
> Private Sub Combobox4_Change() Dim combolist
> If ComboBox4.ListCount = 0 Then Exit Sub
> For Each wks In Worksheets
> If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
> ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
> Next wks
> ' MsgBox (combolist)
> End Sub
>
> Private Sub CommandButton1_Click()
> UserForm1.Hide
> End Sub
>
>
>

[Corey]

Yes.
A list of all sheets that meet the find criteria.
I have placed another post to see if this can be done with a closed
workbook.

Corey....
"crazybass2" <crazybass2@discussions.microsoft.com> wrote in message
news:02B51ABF-24EE-4A59-8539-ED434898518A@microsoft.com...
> Corey,
>
> The code I sent you should give a message box after you make a selection
> in
> Combobox4 (converyor). Are you looking for the same format as the Excel
> Find
> All?
>
> Mike
>
> "Corey" wrote:
>
>>
>> Mike,
>> Thanks again for the time to reply.
>> It works SPOT ON, exactly as i need.
>> It narrows down the 2nd ComboBox to display ONLY values that are on the
>> worksheet that contain a value in the 1st ComboBox.
>> The only thing i need to work on now is to have the ComboBox Value FOUND
>> across all sheets and a list of sheets displayed.
>> I will place this in a New post.
>>
>> Thanks Again Mike,
>> you are a Champion.
>>
>> Corey....
>>
>> Private Sub UserForm_Activate()
>> Dim i As Integer, j As Integer
>> Dim addit As Boolean, addit2 As Boolean
>> ComboBox3.Clear
>> For Each wks In Worksheets
>> addit = True
>> For i = 0 To ComboBox3.ListCount - 1
>> If ComboBox3.ListCount = 0 Then Exit For
>> If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False Next
>> i
>> If addit Then ComboBox3.AddItem wks.Range("B3").Text
>> Next wks
>> End Sub
>>
>> Private Sub ComboBox3_Change() ComboBox4.Clear For Each wks In Worksheets
>> If wks.Range("b3").Text = ComboBox3.Value Then addit2 = True
>> For j = 0 To ComboBox4.ListCount - 1
>> If ComboBox4.ListCount = 0 Then Exit For
>> If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
>> Next j
>> If addit2 Then ComboBox4.AddItem wks.Range("D3").Text End If
>> Next wks
>> End Sub
>>
>> Private Sub Combobox4_Change() Dim combolist
>> If ComboBox4.ListCount = 0 Then Exit Sub
>> For Each wks In Worksheets
>> If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
>> ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
>> Next wks
>> ' MsgBox (combolist)
>> End Sub
>>
>> Private Sub CommandButton1_Click()
>> UserForm1.Hide
>> End Sub
>>
>>
>>