Searching Across Worksheets

Searching In Workbook's Sheets


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

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



Read more ...

Excel VBA Backup Workbook

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


       We added a button to the page to run the backup macro, but if desired, the date_backup macro can be run from the Macro Window opened by pressing  Alt + F8  keys.



Read more ...

Excel Star Effect

Star Effect In Sheet

         Really a funny Excel effect . 
If the button is pressed , the sheet is populated with colorful stars.

excel effect


Read more ...

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.

       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.
Dim tarih1, tarih2 As Date: Dim ara As Range, LastRow As Long
    Dim s1 As Worksheet
    Application.ScreenUpdating = False
    Set s1 = Worksheets("Sayfa1")
    If TextBox1.Value = "" Or TextBox2.Value = "" Then
    MsgBox "Please Enter Date", vbDefaultButton1
    Exit Sub
    End If
    If ComboBox1.Value = "" Then
    MsgBox "Please Choose Product", vbDefaultButton1
    Exit Sub
    End If
    tarih1 = VBA.Format(TextBox1.Value, "dd.mm.yyyy")
    tarih2 = VBA.Format(TextBox2.Value, "dd.mm.yyyy")
 
    ListBox1.Clear
    ListBox1.ColumnCount = 9
    ListBox1.ColumnWidths = "30;170;40;70;60;90;110;50;100"
 
    LastRow = s1.Range("B" & Rows.Count).End(xlUp).Row
    For Each ara In s1.Range("B2:B" & LastRow)
    If CLng(CDate(ara.Value)) >= CLng(CDate(tarih1)) And _
    CLng(CDate(ara.Value)) <= CLng(CDate(tarih2)) And _
    CStr(ara.Offset(0, 1).Value) = CStr(ComboBox1.Text) Then
ListBox1.AddItem
            ListBox1.List(ListBox1.ListCount - 1, 1) = ara
            ListBox1.List(ListBox1.ListCount - 1, 0) = ara.Offset(0, -1)
            ListBox1.List(ListBox1.ListCount - 1, 1) = ara.Offset(0, 1)
            ListBox1.List(ListBox1.ListCount - 1, 2) = ara.Offset(0, 2)
            ListBox1.List(ListBox1.ListCount - 1, 3) = ara.Offset(0, 3)
            ListBox1.List(ListBox1.ListCount - 1, 4) = VBA.Format(ara.Offset(0, 4), "#,##.00")
            ListBox1.List(ListBox1.ListCount - 1, 5) = ara.Offset(0, 5)
            ListBox1.List(ListBox1.ListCount - 1, 6) = VBA.Format(ara.Offset(0, 6), "#,##.00")
            ListBox1.List(ListBox1.ListCount - 1, 7) = ara.Offset(0, 7)
            ListBox1.List(ListBox1.ListCount - 1, 8) = ara.Offset(0, 8)
         
        End If
 Next ara
 Call uzat
 Application.ScreenUpdating = True

The date userform is used  to enter date automatically into text boxes.


Before listing the filtered data in the listbox, the userform elongation effect increases the height of the userform and the listbox appears.




Read more ...

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.

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



Read more ...

Useful Macros - 8 : Automatic Data Transmission Between Sheets

Automatic Data Transmission Between Sheets Of Workbook

 A great excel data copying example.     
 The entered values into  the range A1: R4000 of Sheet1 are automatically copied to other sheets of the workbook.

 We added to copy the following VBA codes to Worksheet_Change method of Sheet1:
Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets.FillAcrossSheets (Worksheets("Sheet1").Range("A1:R4000"))
End Sub




Read more ...

Userform With Scrollbar

Using VBA Scrollbar & Slider Control To Select Listbox Items


      

       excel userform scrollbar slider

excel userform

Read more ...