ComboBox Row Source across ALL sheets

[Corey]

I am trying to setup a search across ALL sheets(sheet amount varies) to find
a MATCHED value in Cell D3.
I want the FIND ALL option to display ALL sheets that contain that value in
cell D3.

How can i enter the Row Source into the Combo Box to include ALL sheets from
just Sheet1?
Currently i have (=1!D3). BUT this ONLY searches Sheet"1" not ALL sheets int
he workbook.

Corey....

 

[Corey]

For starters i want the ComboBox list to LIST all values in cell D3 in ALL
sheets.

How?

 

[Leith Ross]

Hello Corey,

I believe you are going to have to use a macro to do this. The row
source property links to either a cell or range of cells on a single
worksheet. When you think about it, that makes sense. Place this code
in a VBA module.


Code:
--------------------

Sub MatchAll(Value_To_Match As Variant)

Dim Wks As Worksheet

For Each Wks In Worksheets
If Wks.Range("D3").Value = Value_To_Match Then
ComboBox1.Add Wks.Name
End If
Next Wks

End Sub

--------------------


Add this code to your "Match All" button...

Call MatchAll(<value to match>)

Sincerely,
Leith Ross

 

[Leith Ross]

Hello Corey,

To place all the D3 values in the ComboBox, change the code to...


Code:
--------------------

Sub ListAll()

Dim Wks As Worksheet

For Each Wks In Worksheets
ComboBox1.Add Wks.Range("D3").Text
Next Wks

End Sub

 

[Corey]

Thanks for the reply Leith.

I created a new module and placed the code in it.

I placed the Call MatchAll(<value to match>) intot the code for
the OK button.

What do i place as the <value to match> in it ? ComboBox3 ??

I do not get any values int he combobox3 list also??

Corey....

 

[Corey]

Leith,
The :
Sub ListAll()

Dim Wks As Worksheet

For Each Wks In Worksheets
ComboBox3.Add Wks.Range("D3").Text
Next Wks

End Sub

Where do i place it, in the combobox3 code or the OK button code?
Sorry but getting a little confused where to put what code...
I cannot seem to get any of the 'D3" values to be diplayed in the combobox3
list as yet.


Corey....

 

[Leith Ross]

Hello Corey,

Sorry for the delay. I went out to dinner. The ListAll code doesn't
need to be in a VBA module. You can place the ListAll code in the
UserForm_Activate() event, if you are using a user form. If not place
the code in the button's click event.

Sincerely,
LeithRoss

 

[Corey]

_______________
Dim Wks As Worksheet
For Each Wks In Worksheets
ComboBox1.Add Wks.Range("D3").Text
Next Wks

End Sub
_______________
I placed the code above in the Userform sub, whicj gave me as follows:

Private Sub UserForm_Click()
Dim Wks As Worksheet
For Each Wks In Worksheets
combobox3.Add Wks.Range("D3").Text
Next Wks

End Sub

But there is No values that are displayed in the combo box??
I have 2 comboboxes in this userform(UserForm3).
1 for a customer name &
1 for a conveyor name

The purpose is to quickly FIND a specific conveyor at a specific customers
site.
(Have to add the customer as more than 1 site may have a Conveyor 1)

Both combobox RowSources do NOT have anything in them.

I have not progressed into the Search function (Match) as i can yet get the
List to fill from the worksheet values in "D3".

Corey....

 

[Norman Jones]

Hi Corey,

Try changing:

combobox3.Add Wks.Range("D3").Text

to

ComboBox31.AddItem Wks.Range("D3").Text

 

[Corey]

Norman,
I changed the line as requested but still get NO values int eh combobox??
************************
Private Sub UserForm_Click()
Dim Wks As Worksheet

For Each Wks In Worksheets
combobox31.AddItem Wks.Range("D3").Text
Next Wks


End Sub
*************************

Corey....

 

[Norman Jones]

Hi Corey,

Providing that: at least one sheet had a poulated D3 cell and that I clicked
the Userform, the Combobox3 is filled for me, as expected.

Incidentally, I note that you now refer to ComboBox31 as opposed to the
earlier ComboBox3.

 

[Norman Jones]

Hi Corey,

Re-reading, I see that the ComboBox31 was my typo and therefore:

should be:

ComboBox3.AddItem Wks.Range("D3").Text

 

[Corey]

Norman,
I still get nothing.
I thought the fact that i have merged cells D3:F3, might have made a
difference, but when i removed the merged cells in 1 sheet, i still got
nothing.
The code i have:

Private Sub UserForm_Click()
Dim Wks As Worksheet
For Each Wks In Worksheets
combobox3.AddItem Wks.Range("D3").Text
Next Wks
End Sub


So according to you, this should populate the ComboBox List with Values from
ALL Sheets that have a vlaue in Cell D3 ??

What else could be wrong with my code?

RowSource is empty too

Corey....

--
Regards

Corey McConnell

Manager - Splice Tech Unanderra Pty Ltd
P - 02 4272 8822
F - 02 4272 8833
M - 0408 402 522
E - (e-mail address removed)

 

[Norman Jones]

Hi Corey,

The code works for me, subject to the indicated provisos.

In this connection, why are you using the Userform_Click event to populate
the combobox. Why not use the Userform_Activate event:

'=============>>
Private Sub UserForm_Click()
Dim Wks As Worksheet
For Each Wks In Worksheets
ComboBox1.AddItem Wks.Range("D3").Text
Next Wks
End Sub
'<<=============

If you wish, I can send you my test workbook in response to an email:

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )