Dim Rw As Long
Dim Cap As IntegerSub 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
