Searching Across Worksheets

Searching In Workbook's Sheets

           In this example, the entered value in A2 cell (name) is searched in the workbook pages .

Found values are reported with its addresses   in "Search Page".
The "names" are in column 3 of the pages.That's why the searching took place in column 3 .Codes are created accordingly :

If aranan = Cells(y, 3) Then
s1.Cells(e, 1) = Sheets(a).Name
s1.Cells(e, 2) = Cells(y, 3).Address
s1.Cells(e, 3) = Cells(y, 1)
s1.Cells(e, 4) = Cells(y, 2)
s1.Cells(e, 5) = Cells(y, 3)
s1.Cells(e, 6) = Cells(y, 4)
s1.Cells(e, 7) = Cells(y, 5)
End If


Excel Insert Image Into Cell Automatically

Insert Pictures Automatically Based On Cell Value

     
        According to the value entered  in any cell of column A , the image is added automatically into Column E. Images are resized to fit cells.

Names of the pictures with part numbers in column A are same.

Codes that we used in this Excel template :
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If Target.Row Mod 20 = 0 Then Exit Sub
On Error GoTo son
ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & Target.Value & ".jpg").Select
Selection.Top = Target.Offset(0, 2).Top
Selection.Left = Target.Offset(0, 4).Left
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = Target.Offset(0, 2).Height
Selection.ShapeRange.Width = Target.Offset(0, 4).Width
Target.Offset(1, 0).Select
son:

         We added the pictures and the workbook into the same folder so that each user can run the template on their computer without problems .
 When inserting images into the page, there is no problem that the "image path is not found" (ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & Target.Value & ".jpg").Select) .



Insert Picture Dynamically In Cell Based On Cell Value & Delete Picture

         The template we created in the tutorial above did not have the ability to delete images.
We have created a new template, in which the image from the folder is found according to the cell value and added to the cell in column E.
If the cell value in column A is deleted or changed, the image for that value is removed from the cell in column E.

Updated Userform

Updated Advanced Userform Example

            I made some changes in the UserForm codes that  I've done it before :

-  Copying the filtered data into Page 2
While entering data if there are duplicate values in a column ,can be warning with msgbox.
- The listbox  has got 12 columns.

It's other features :

- Data Adding

- Data Delition

- Data Update

- Searching with The Userform

- Progress Bar Feature

- Scrolling the Listbox with Spin Buttons

- Next ,Previous ,First ,Last Recording Buttons


The Workbook Backup

Useful Macros - 9
Workbook Backup With Vba

     When the button is pressed, the workbook is copied to Documents folder . The copied backup workbook is named as "Backup mm-dd-yy hh-mm.xls".

Our procedure:
Sub date_backup()
Dim zaman, isim As String
zaman = Application.Text(Now(), "mm-dd-yy hh-mm")
isim = "Backup" & zaman & ".XLS"
ActiveWorkbook.SaveCopyAs isim
End Sub


Filter With Text Boxes In Worksheet

 Filter  With Text Boxes In Worksheet

         Our template has got 56.666 rows.That is a fairly large worksheet.

But don't worry. Searching and filtering can be done easily with  text boxes in the page.Besides can be filtering using multiple criteria inputs from text boxes.
You can use this example for your own template.





Example code -for textbox1-  :

Private Sub TextBox1_Change()
On Error Resume Next
metin = TextBox1.Value
Set bul = Range("j4:j").Find(What:=metin)
Application.Goto Reference:=Range(bul.Address), Scroll:=False
Selection.AutoFilter field:=10, Criteria1:=TextBox1.Value & "*"
If metin = "" Then
Selection.AutoFilter
[j4].Activate
End If
End Sub


Display Images Dynamically Based On Cell Contents

Display Images In  Worksheet With Vba Codes

           When a cell in column A is selected,  picture of the cell contents is shown in column F.
As well as the background color of the cell is yellow.
Images are placed on the "Images" folder.
Important point is that ,names of the pictures with names of product in column A are same.
If image in column F is clicked , the image invisible.




Excel Star Effect

Star Effect In Sheet

         Really A Funny Effect . If the button is pressed , the sheet is populated with colorful stars.


Advanced Filtering With Userform

The Items Filtering Based On Dates (First-Last Date)


Ago ,the products  are filled  with unique items into combobox and sorted alfabetically.

For this, following codes were used:

Private Sub UserForm_Initialize()
Dim ComBoList      As Variant, LastRow&, cell As Range
Dim ComBoTemp       As Variant, x, j     As Long
  
Application.ScreenUpdating = False
With Worksheets("Sayfa1")
On Error Resume Next
.ShowAllData
Err.Clear
LastRow = .Cells(Rows.Count, 3).End(xlUp).Row
.Range("C2:C" & LastRow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ComboBox1.Clear
For Each cell In .Range("C2:C" & LastRow).SpecialCells(12)
ComboBox1.AddItem cell.Value
Next cell
On Error Resume Next
.ShowAllData
Err.Clear
End With
   
 ComBoList = Me.ComboBox1.List
    For x = LBound(ComBoList) To UBound(ComBoList) - 1
        For j = x + 1 To UBound(ComBoList)
            If ComBoList(x, 0) > ComBoList(j, 0) Then
                ComBoTemp = ComBoList(x, 0)
                ComBoList(x, 0) = ComBoList(j, 0)
                ComBoList(j, 0) = ComBoTemp
            End If
        Next j
    Next x
End Sub

       When the dates (first date,last date) are entered in text boxes and if report button is pressed  the userform elongation effect is activated and listbox appears. Products can be filtered on listbox.
The date userform is used  to enter date automatically into text boxes.

Option Buttons Usage In Excel Userform

Using Option Button Controls In Excel Userform -Flight Information Registration Form


           This userform is prepared for recording the flight stats.
There are a lot of option buttons (form control) in this userform. Option buttons are activated or not activated depending on the situation.For example :

Private Sub OptVIP_Click()
    OptChild.Enabled = False
    OptInfant.Enabled = False
    OptinJ = True
    OptRtnJ = True
    OptinJ.Enabled = False
    OptRtnJ.Enabled = False
    OptInY.Enabled = False
    OptinZ.Enabled = False
End Sub
Private Sub OptAdult_Click()
    OptUnMin.Enabled = False
    OptYoungPers.Enabled = False
End Sub
Private Sub OptChild_Click()
    OptUnMin.Enabled = True
    OptYoungPers.Enabled = True
End Sub

Also the date userform is used , to enter date automatically into text boxes.

excel option buttons


Useful Macros - 8 : Automatic Data Transmission Between Sheets

Automatic Data Transmission Between Sheets Of Workbook

Writing Written Text Into Sheet 1 (between A1 and R4000 cells), Automatically To Other Pages. The used code :

"Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets.FillAcrossSheets (Worksheets("Sheet1").Range("A1:R4000"))
End Sub"


Userform With Scrollbar

Using VBA Scrollbar & Slider Control To Select Listbox Items

            If you remember, we used spin buttons in other listbox examples (review New Excel Userform).
With the spin button can be adjusted up or down by only one unit at a time.The VBA scrollbar can be used as more efficient from spin buttons for setting of the adjusting a large range of values  .

In our VBA userform example ,through scrollbar or slider control can be navigated between the data listed in the listbox. We chose to use a horizontal scrollbar and slider for this purpose.

Scrollbar and slider control are dependent on each other. For this purpose, we used the following codes:
Private Sub ScrollBar1_Change()
Dim a As Integer, sat As Long
CommandButton1.Enabled = False
CommandButton2.Enabled = True
CommandButton3.Enabled = True
Slider1 = ScrollBar1
Label4 = ScrollBar1
ListBox1.ListIndex = ScrollBar1 - 1
For a = 0 To 6
Controls("textbox" & a + 1) = Cells(ScrollBar1 + 1, a + 2)
Next
TextBox8 = VBA.Format(Cells(ScrollBar1 + 1, 9), "dd.mm.yyyy")
Range("A2:I" & [a65536].End(3).Row).Interior.ColorIndex = 25
sat = ScrollBar1 + 1
Range("A" & sat & ":I" & sat).Interior.ColorIndex = 6
End Sub
Private Sub Slider1_Change()
ScrollBar1 = Slider1
End Sub

           When the Userform is loaded, the data in the worksheet is listed automatic on the listbox as columns.  By the Userform, a new record can be added to worksheet, changed or deleted.
Automatic id number is assigned for each new record.


         I used a button to increase and decrease the height of userform (to unhide scrollbar & slider). I created a Vba loop and assigned it to the button.At the same time ,this is a nice userform animation.
When creating the Excel userform, I set a height value, when the button is pressed, the userform becomes longer if the height is less than this value, and the userform becomes shorter if it is larger.

excel userform


excel userform

Userform With Multiple Pages

Excel Multipage Control On Userform 

       As example ,we have a list of personnel. We will use a userform when editing the details about personnel. The userform with multipage control provides more faster data entry on the worksheet.        
       VBA multipage control contains two pages. At page 1,the user can fill in personnel informations (name,address,city,phone,birthdate,birthplace etc.)
At page 2, items of combobox (for choosing city)  can be added.

Also we added a menu bar on the userform. Items of this menu :
✅ New
✅ Open
✅ Save
✅ Save As
✅ Print Preview
✅ Print
✅ Close


multipage control on userform

excel userform menu

Also these controls can be used in this userform :
– Adding new record
– Deleting record
– Updating record
– Label to see total data
– Navigating between items of listbox  with spin buttons (down/up)
– Assigning sort numbers for each record (when  an item is deleted from  listbox, the sequence numbers are set again.)