VBA for search confusion...

[lucky12341]

I need to know if something is possible with the code I am using or is there a way to change the code to do this.
The VBA code listed below searches sheet10 in the A column for whatever I type into txtLocation. I need to know if there is a way to use a checkbox type system to be able to search through multiple sheets (based on selected checkboxes). I know how to implement the If Then Statements but I cannot figure out how to set the range to keep everything working based on the checkboxes.

Private Sub cmdLocationFind_Click()
    Dim strFind, FirstAddress As String   'what to find
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet10.Range("a3", Range("a10425").End(xlUp))
    strFind = Me.txtLocation.Value    'what to look for
    Dim f As Integer
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .txtPO.Value = c.Offset(0, 1).Value
                .txtItemNo.Value = c.Offset(0, 2).Value
                .txtPN.Value = c.Offset(0, 3).Value
                .txtPcMk.Value = c.Offset(0, 4).Value
                .txtDescription.Value = c.Offset(0, 6).Value
                .txtHIC.Value = c.Offset(0, 7).Value
                .txtOQty.Value = c.Offset(0, 8).Value
                .txtCQty.Value = c.Offset(0, 5).Value
                .cmdAmend.Enabled = True     'allow amendment or
                .cmdDelete.Enabled = True    'allow record deletion
                .cmdAdd.Enabled = False      'don't want to duplicate record
                .cmdLocationFindAll.Enabled = True
                .cmdPOFindAll.Enabled = False
                .cmdItemNoFindAll.Enabled = False
                .cmdPNFindAll.Enabled = False
                .cmdPcMkFindAll.Enabled = False
                .cmdDescriptionFindAll.Enabled = False
                .cmdHICFindAll.Enabled = False
                
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                MsgBox "There are " & f & " instances of " & strFind
                Me.Width = 500
                Me.Height = 426.75
            End If
        Else: MsgBox strFind & " not listed as a Location."    'search failed
        End If
    End With
End Sub

 

[lucky12341]

Anyone any ideas please...?

 

[Joe Was]

The key is to set the new working range based upon which CheckBox is Checked. You can do this in a number of ways, if you have a lot of them then use the "Select Case" structure. If you only have a few, then use the "If" test.

So for your:

Set rSearch = Sheet10.Range("a3", Range("a10425").End(xlUp))

Try:

Sheets("Sheet10").Select
If ActiveSheet.CheckBox1 = True Then
CheckBox2 = False
CheckBox3 = False

Sheets("Sheet2").Select
Set rSearch = Sheet2.Range("a3", Range("a10425").End(xlUp))
End If

If ActiveSheet.CheckBox2 = True Then
CheckBox1 = False
CheckBox3 = False

Sheets("Sheet11").Select
Set rSearch = Sheet11.Range("a3", Range("a10425").End(xlUp))
End If

If ActiveSheet.CheckBox3 = True Then
CheckBox2 = False
CheckBox1 = False

Sheets("Sheet8").Select
Set rSearch = Sheet8.Range("a3", Range("a10425").End(xlUp))
End If

 

[lucky12341]

That makes sense, the issue I am coming up with though is I need to search more sheets then one. I need to be able to select 2 or 3 checkboxes that will inturn search other sheets, potentially up to all the checkboxes, is this possible?

 

[Joe Was]

'Your CheckBox code here!

Set S = Sheets.Application

For Each S In Application.Sheets
With S.Range("A1:IV65536")
If WorkSheets.Name <> "Sheet1" Or _
WorkSheets.Name <> "Sheet8" Or _
WorkSheets.Name <> "Sheet10" Then Exit Sub

'Then your search code here!

Next S

 

[lucky12341]

Joe Was,
For the 'Your Checkbox code here! do I use the style format from the first example you gave for all the checkboxes? Or does the style change?

 

[Joe Was]

No.

You will need to load an Array of Sheet Names based upon which CheckBox's have been checked to direct which Sheets get Searched. Only you can come up with the exact code based upon your data, sheets and options!