Saturday, July 30, 2016

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.


Wednesday, July 27, 2016

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

Friday, July 22, 2016

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.


Thursday, July 14, 2016

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.


Wednesday, July 13, 2016

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


Friday, July 8, 2016

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





Thursday, July 7, 2016

Add Items Into Combobox In Alphabetic Order

            When userform is opened ,Combobox on the userform is filled with values in Column B alphabatically.
Data are pulled into text boxes according to the selected value from the combobox.

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.


Wednesday, July 6, 2016

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.


Friday, July 1, 2016

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).