Searching A Value Across An Entire Workbook With Userform

Excel Find A Value In Workbook With Userform - Reporting The Results



A great userform example for searching data on the selected sheet or the entire workbook.

      As an alternative to Excel Ctrl + F window, this userform can be used. In this template ; unlike from data searching with Ctrl + F, the found results are saved to the created a report sheet(ReportSheet).


Also ,for the found results, are created hyperlinks when they are clicked that gone to cell address with this procedure :
Sub Create_Hyperlinks()
Dim LArray() As String, a As Long
For a = 2 To Sheets("ReportSheet").Cells(Rows.Count, 1).End(xlUp).Row
     ActiveSheet.Hyperlinks.Add Anchor:=Range(Cells(a, 1), Cells(a, 2)), Address:="", SubAddress:=Cells(a, 1) & "!" & Cells(a, 1).Offset(0, 1)
    Next a
 End Sub

Firstly, when the userform opens, the sheets of the workbook are automatically listed in the drop-down list (VBA combobox control) :
Dim i As Byte
UserForm2.ComboBox1.AddItem "ALL SHEETS"
For i = 1 To Worksheets.Count
         UserForm2.ComboBox1.AddItem Worksheets(i).Name
    Next i

➥ Using textbox on userform ,data can be searched  in the selected sheet from the combobox or in all sheets as a part or as a whole.
The found results are listed on a listbox control. User can navigate between the items of the listbox by VBA spin button control .

Private Sub SpinButton1_SpinDown()
On Error Resume Next
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub
With Me.ListBox1
        .ListIndex = .ListIndex + 1
    End With
 End Sub
Private Sub SpinButton1_SpinUp()
On Error Resume Next
If ListBox1.ListIndex = 0 Then Exit Sub
With Me.ListBox1
        .ListIndex = .ListIndex - 1
End With
End Sub

➥ When the selected item on the listbox is clicked,can be went to item's cell address.



Read more ...

Filter Between Two Dates With Userform

Excel Filter Between Two Dates Using ADODB Recordset


            In this example ,the records between two dates were filtered  with userform. Also filtered data can be copied to other sheet with a button.


We made the filtering between dates by using Adodb.Connection and Adodb.Recordset method. 


excel filter between two dates adodb

Read more ...

Filtering Between Two Specific Dates In Excel

Filling Combobox With Uniqe And Ascending Order Dates

            We ago have  created combo boxes containing  dates that unique ascending order sorted . For this process we have used Ado Connection. The dates in Column B were populated into two combo boxes.



             Later we've done filtering by the selected dates from drop-down lists with buttons.

We have added the following code to enter only date (as "dd.mm.yyyy") in the Column B :
"Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range
      If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    If Target.Address = "$B$1" Then Exit Sub
    For Each rng2 In Range(Target.Address)
    If rng2.Value = "" Then
    MsgBox "You Must Enter A Valid Date (20.10.2015 etc.)", vbCritical, ""
    rng2.Activate
    Exit Sub
    End If
        If IsDate(rng2.Value) = False Then
         rng2 = Empty
        Exit Sub
        Else
            rng2.Value = CDate(rng2)
        End If
    Next rng2

Read more ...

Creating A Combobox Containing Only Unique Distinct Alphabetically Sorted Values

             Sometimes unique values need to fill the combobox and  need to sort alphabetic..In this way, the processes may be easier.

The cells in Column A were selected  to fill combobox in this example :
For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row  -    "1" in the code indicates Column A.

Also,data is filtered with combobox and  copied to the other pages in our study.

The used codes to fill combobox unique values and to sort alphabetic :

Dim x, a, b As Long, c As Variant
'Unique Records
For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Range("A2:A" & x), Cells(x, 1)) = 1 Then
ComboBox1.AddItem Cells(x, 1).Value
End If
Next
'Alphabetic Order
For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
        If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = c
       End If
  Next
  Next


Read more ...

Add Items Into Combobox In Alphabetic Order

Excel VBA Automatically Fill Text Boxes Based On A Selection Combobox 


        When userform is opened ,Combobox on the userform is filled with values in Column B alphabatically. Our codes that we used to fill combobox as alphabatically  :
Sub fill_combobox()
Dim LastRow, a, b As Long, c As Variant
ComboBox1.Clear
LastRow = Sheets("S1").Cells(Rows.Count, 2).End(xlUp).Row

For x = 2 To LastRow
ComboBox1.AddItem Cells(x, 2).Value
Next
  For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
        If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = c
       End If
  Next
  Next
End Sub

       Data are filled into text boxes based on selection the combobox. With that loop in ComboBox1_Change() procedure:
For Each bul In Sheets("S1").Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    If bul = ComboBox1 Then
    i = 0
      bul.EntireRow.Activate
        TextBox1 = bul.Offset(i, 1)
        TextBox2 = bul.Offset(i, 2)
        TextBox4 = bul.Offset(i, 3)
        TextBox5 = bul.Offset(i, 4)
        TextBox6 = bul.Offset(i, 5)
        TextBox7 = bul.Offset(i, 6)
        TextBox8 = bul.Offset(i, 7)
     End If
    Next bul

Also,combobox is sorted in alphabetical order when new data is added and  deleted.

The id numbers (1-2-3-4 etc.) are automatically added to Column A. Id numbers are generated automatically again  when new record is added to the worksheet and  when record is removed from combobox.



Read more ...

Creating Excel Animation With VBA Codes

Excel Animation Loop


         When first button is clicked, the circle shape moves to the right while turning itself around.

If second button is clicked ,this action is repeated 10 of times.Simple and beautiful loop example.


excel animation sample



Read more ...