Database

Dim Rw As Long
Dim Cap As Integer
Sub HighLight1(Ctrl As Control)
    With Ctrl
        If .Value = True Then
            .BackColor = RGB(255, 255, 0)
        Else
            .BackColor = vbButtonFace
        End If
    End With
End Sub
Sub HighLight2(Ctrl As Control)
    With Ctrl
        If .Value = True Then
            .BackColor = RGB(0, 255, 255)
        Else
            .BackColor = vbButtonFace
        End If
    End With
End Sub
Private Sub CheckBox1_Change()
    HighLight2 CheckBox1
End Sub
Private Sub CheckBox2_Click()
    HighLight2 CheckBox2
End Sub
Private Sub CheckBox3_Click()
    HighLight2 CheckBox3
End Sub
Private Sub cmdAmend_Click()
Dim DataRw As Long
For Cap = 1 To 28
DataRw = Sheet5.Cells(Rw, 29).Value
Sheet1.Cells(Rw, Cap).Value = Me("tbx" & Cap).Value
Next Cap
End Sub
Private Sub CommandButton1_Click()
'SEARCH
    Dim C As Variant
    Dim Col As Variant
    Dim Ctrl As Object
    Dim Data As Variant
    Dim DstWks As Worksheet
    Dim BtnNumber As Integer
    Dim FirstAddx As String
    Dim FoundIt As Range
    Dim i As Integer
    Dim R As Long
    Dim rng As Range
    Dim RngEnd As Range
    Dim SrcWks As Worksheet
    Set SrcWks = Worksheets("Data")
    Set DstWks = Worksheets("Search Details")
    With Frame1.Controls
        For i = 0 To .Count - 1
            If .Item(i).Value = True Then
                BtnName = .Item(i).Name
                Exit For
            End If
        Next i
    End With
    Select Case BtnName
    Case "OptionButton1"
        Col = 1: Data = TextBox1: GoSub DataSearch
        Col = 2: Data = TextBox2: GoSub DataSearch
    Case "OptionButton2"
        Col = 3: Data = TextBox1: GoSub DataSearch
    Case "OptionButton3"
        Col = 4: Data = TextBox2: GoSub DataSearch
    Case "OptionButton4"
        Col = 18: Data = TextBox2: GoSub DataSearch
    End Select
    Exit Sub
DataSearch:
    With DstWks
        Set RngEnd = .Cells.Find("*", [A1], xlFormulas, xlWhole, xlByRows, xlPrevious, False)
        R = RngEnd.Row
        R = IIf(R < 2, 2, R + 1)
    End With
    With SrcWks
        Set rng = .Cells(2, Col)
        Set RngEnd = .Cells(Rows.Count, Col).End(xlUp)
        Set RngEnd = IIf(RngEnd.Row < rng.Row, rng, RngEnd)
        Set rng = .Range(rng, RngEnd)
    End With
    Data = Trim(Data)
    Set FoundIt = rng.Find(What:=Data, After:=rng.Cells(1, 1), _
                           LookIn:=xlFormulas, LookAt:=CheckBox2.Value + 2, _
                           SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                           MatchCase:=CheckBox1.Value)
    If Not FoundIt Is Nothing Then
        FirstAddx = FoundIt.Address
        FoundIt.EntireRow.Resize(1, 28).Copy Destination:=DstWks.Cells(R, 1)
        If CheckBox3.Value = False Then Exit Sub
        Do
                DstWks.Cells(R, 29).Value = FoundIt.Row
            FoundIt.EntireRow.Resize(1, 28).Copy Destination:=DstWks.Cells(R, 1)
            Set FoundIt = rng.FindNext(FoundIt)
            R = R + 1
        Loop While FoundIt.Address <> FirstAddx And Not FoundIt Is Nothing
        Me.ListBox1.RowSource = DstWks.UsedRange.Address(external:=True)
        Me.Height = 425  ' 391
    Else
        MsgBox "No Match was found for '" & Data & " '", vbExclamation
    End If
End Sub
Private Sub CommandButton2_Click()
'CLOSE
    Me.Hide
    Unload Me
End Sub
Private Sub CommandButton3_Click()
'RESET FORM
    TextBox1.Value = ""
    TextBox2.Value = ""
    OptionButton1.Value = True
    CheckBox1.Value = False
    CheckBox2.Value = False
    CheckBox3.Value = True
    TextBox1.SetFocus
End Sub
Private Sub CommandButton4_Click()
Dim tbl As Range
Dim Answer As String
    Answer = MsgBox("This will remove any searches you have already done." & vbCrLf _
                  & "Are you sure you want to clear Search Details?", vbQuestion + vbYesNo)
    If Answer = vbYes Then
    Set tbl = Worksheets("Search Details").Range("A2").CurrentRegion
 tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
    tbl.Columns.Count).ClearContents
    End If
End Sub
Private Sub ListBox1_Click()
Rw = Me.ListBox1.ListIndex + 1
With Me
.Width = 750    ' 548
    For Cap = 1 To 28
    Me("lbl" & Cap).Caption = Sheet5.Cells(1, Cap).Value
    Me("tbx" & Cap).Value = Sheet5.Cells(Rw, Cap).Value
    Next Cap
    End With
End Sub
Private Sub OptionButton1_Change()
    HighLight1 OptionButton1
End Sub
Private Sub OptionButton1_Click()
    If OptionButton1.Value = True Then
        Label1.Caption = "Car Manufacturer"
        TextBox1.Visible = True
        Label2.Caption = "Car Model"
        TextBox2.Visible = True
        TextBox1.SetFocus
    End If
End Sub
Private Sub OptionButton2_Change()
    HighLight1 OptionButton2
End Sub
Private Sub OptionButton2_Click()
    If OptionButton2.Value = True Then
        Label1.Caption = "Model Type"
        TextBox1.Visible = True
        Label2.Caption = ""
        TextBox2.Visible = False
        TextBox1.SetFocus
    End If
End Sub
Private Sub OptionButton3_Change()
    HighLight1 OptionButton3
End Sub
Private Sub OptionButton3_Click()
    If OptionButton3.Value = True Then
        Label1.Caption = ""
        TextBox1.Visible = False
        Label2.Caption = "Litre"
        TextBox2.Visible = True
        TextBox2.SetFocus
    End If
End Sub
Private Sub OptionButton4_Change()
    HighLight1 OptionButton4
End Sub
Private Sub OptionButton4_Click()
    If OptionButton4.Value = True Then
        Label1.Caption = ""
        TextBox1.Visible = False
        Label2.Caption = "Colour"
        TextBox2.Visible = True
        TextBox2.SetFocus
    End If
End Sub
Private Sub UserForm_Activate()
Me.CommandButton3.Value = True
End Sub
Private Sub UserForm_Initialize()
Me.Height = 220
Me.Width = 384
End Sub