Find ALL WorkSheets according to 2 ComboBox Values.... Help with Code

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

In my quest to perform a coded FIND i have reached the following code/s.

The first code is for the FIND.
The second is the Cell Values from 2 ComboBoxes.


_____________________________________________________________

Private Sub userform3OK_Click()
' FIND Code

Cells.Find(What:=ComboBox4&ComboBox3, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

End Sub

__________________________________________________________________


Private Sub Userform_Activate()
' ComboBoxes and Userform Code
Dim wks As Worksheet
For Each wks In Worksheets
ComboBox3.AddItem wks.Range("B3").Text
Next wks
For Each wks In Worksheets
ComboBox4.AddItem wks.Range("D3").Text
Next wks

End Sub

___________________________________________________________________

I can yet get the FIND Code to Display (Somehow) ALL Sheets that Contain
both the ComboBox3 & ComboBox4 Values in a Single WorkSheet.
Like in a Manual (CTR+F) Find All Sheets FIND.

Any idea's.

Corey....
 
OK Just got further now, thanks to Mikes reply to another of my posts.

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


HOW CAN I ADD TO THIS A FIND CODE TO DISPLAY ALL SHEETS THAT CONTAIN THE
USERFORM VALUE ??

Corey....
 
Here is some code. It creates a collection of all of the sheets that meet
both criteria...

Public Sub ReturnSheets()
Dim colSheets As Collection
Dim str1 As String
Dim str2 As String
Dim wks As Worksheet

str1 = "this"
str2 = "that"
Set colSheets = New Collection
For Each wks In Worksheets
If ContainsString(str1, wks) And ContainsString(str2, wks) Then
colSheets.Add wks, wks.Name
End If
Next wks

If colSheets.Count = 0 Then
Set colSheets = Nothing
MsgBox "Sorry..."
Else
For Each wks In colSheets
MsgBox wks.Name
Next wks
End If
End Sub

Public Function ContainsString(ByVal InputText As String, wks As Worksheet)
As Boolean
Dim rng As Range

Set rng = wks.Cells.Find(What:=InputText, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rng Is Nothing Then
ContainsString = False
Else
ContainsString = True
End If
End Function
 
Thanks for the code Jim,
I am getting an eror at:



If ContainsString(str1, wks) And ContainsString(str2, wks) Then

Saying:
'Compile Error'
Sub or Function Not Defined....

???

Any Idea's

Corey....
 
Jim,

The code Corey has already outputs a list of all the sheets containing a
match for CB3 & CB4. He's got it commented out in the code he posted. I
think what he is looking for is to have the actual Excel FIND ALL dialog come
up with the appropriate sheets.
 
Corey,

Using the Excel Find... menu will not allow you to do what you want. It
searches each cell individually and therefore would find all the sheets with
"Fred" in it or all the sheets with "CV2" in it, but not both at the same
time.

Is there some reason why you need it to got through the Excel Find...? You
can have the list of sheets populate a message box (as I did in the code I
sent you) or you can have it write to a sheet, or an external file, etc.

Mike
 
Back
Top