ComboBox Row Source across ALL sheets
 

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....

ComboBox Row Source across ALL sheets
 

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

How?

ComboBox Row Source across ALL sheets
 

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
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557954

ComboBox Row Source across ALL sheets
 

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


--
Leith Ross


------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557954

ComboBox Row Source across ALL sheets
 

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....

ComboBox Row Source across ALL sheets
 

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....

ComboBox Row Source across ALL sheets
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557954

ComboBox Row Source across ALL sheets
 

_______________
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....

ComboBox Row Source across ALL sheets
 

Hi Corey,

Try changing:

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

to

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

---
Regards,
Norman


"Corey" wrote in message
...

_______________
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....

ComboBox Row Source across ALL sheets
 

Hi Corey,

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

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

should be:

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

---
Regards,
Norman

ComboBox Row Source across ALL sheets
 

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 -

"Norman Jones" wrote in message
...
Hi Corey,

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

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

should be:

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

---
Regards,
Norman

ComboBox Row Source across ALL sheets
 

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



---
Regards,
Norman



"Corey" wrote in message
...
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 -