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

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

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

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

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

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

Jim Thomlinson


"Corey" wrote:

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

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

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

Jim Thomlinson


"Corey" wrote:

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

Find ALL WorkSheets according to 2 ComboBox Values.... Help wi
 

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.

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Corey" wrote:

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

Find ALL WorkSheets according to 2 ComboBox Values.... Help wi
 

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

"Corey" wrote:

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