Searching A Value Across An Entire Workbook With Userform

            TextBox on the userform was used for searching in this study.

            As an alternative to Ctrl + F, userform can be used.Unlike from Ctrl + F in this template,the found results are saved  to the created "report page".

Found results are listed on a listbox. The value can be searched into whole a workbook or into worksheet that you selected from drop-down list.



When the items on the listbox clicked,can be went to item's address.

All found results are listed as hyperlinks in the generated report sheet.


Searching A Value Across An Entire Workbook With Textbox And Option Buttons

            The searched value can be easily found in all the workbook with the textbox and option buttons that they are added to the workbook's first sheet .


Through option buttons,value in textbox  can be searched as two types , whole or part :

If Sheets(1).OptionButton1 = True Then
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Else
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End If

All results are listed as hyperlinks in the generated report sheet with different a function.This function's codes :

Function NewSheet(argCreateList)
    For Each Worksheet In ThisWorkbook.Worksheets
        If argCreateList = Worksheet.Name Then
            Exit Function ' if found - exit function
        End If
    Next Worksheet
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = argCreateList
End Function

User can be reached to cells of results when clicked on this hyperlinks :
Sub Create_Hyperlinks()
Dim LArray() As String
For Each cell In Sheets("New_Report").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
 If cell <> "" Then
    LArray = Split(cell.Text, "!")
    ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=LArray(0) & "!" & LArray(1)
     End If
    Next cell
End Sub

The second row were excluded from to the searching ,because of there are the column headings in the second row.

When the result cells are active , the background color of these cells is yellow.



The following template can be used to search only in first sheet :

Searching Accross A Worksheet

Excel Macro To Search Entire Workbook

         With Vba ,it is possible to search an entire workbook, for a searched value. 

We will use The Find Method for this process.In Excel Program, The Find & FindNext Method is a very powerful option in finding data  and is useful.

          When the macro runs , inputbox opens and asks the data we want to find. The found results are listed on the MsgBox  with cell addresses. The process continues until user  stops it.


Filtering Between Two Dates With Userform


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


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
End Sub


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




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.


Creating Excel Animation With Vba Codes


         
          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.


The Address Book That Contains Pictures

             In this study, we created an address book that views the picture according to records.With button, the selected image from computer can be added to the item and can be deleted.

The file path of the selected picture is assigned to the empty cell :(C:\Project_1\NewPictures\TestImage3.jpg) etc.

              This picture is displayed on the userform with Image Control(Image control is used to display a picture on the userform).