Sunday, November 29, 2015

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


Wednesday, November 25, 2015

Insert Picture In Excel 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.


Monday, November 23, 2015

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


Friday, November 20, 2015

Wednesday, November 18, 2015

Searching With Text Boxes In Page

 Searching  With Text Boxes In Worksheet

The template has got 56.666 rows.That is pretty big a page.

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:j65536").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


Monday, November 16, 2015

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.




Sunday, November 15, 2015

Excel Star Effect

Star Effect In Sheet

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


Friday, November 13, 2015

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.

Wednesday, November 11, 2015

Option Buttons Usage In Userform

Using Option Button Controls In Excel -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.


Sunday, November 8, 2015

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"


Thursday, November 5, 2015

Userform With Scrollbar

Userform With Scrollbar And Slider

A wonderful  userform with opening effect.
Scrollbar and slider are used  to view the data in this form.


Sunday, November 1, 2015

Userform With Multiple Pages

Advanced Userform Example - Userform With Multiple Pages

           We made at a template which creates an userform that contains multiple pages.

The 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 new menu on userform. Items of this menu :
- New
- Open
- Save
- Save As
- Print Preview
- Print
- Close




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