Wednesday, December 30, 2015

Excel VBA AutoFit Method

Setting  The Width Of Columns According To The Cell Content

The Life-Saving Excel Feature You Probably Didn’t Notice.

Owing to code, Excel automatically adjusts the width of a column to accommodate the width of the text that takes up the most horizantal space in each row :

" Columns(Target.Column).AutoFit  "


Tuesday, December 29, 2015

Daily Sales Report

Personnel Based Daily Sales Report Template

        In this template ago ,we entered the amount of products sold by each personel. Then we created a separate page for each personnel with “Report” button .In this way,we can add every day to their own pages the amount of products sold by each personnel.

If you want to delete this report pages, can be deleted with “Delete Pages Report” button.Also we added a Combobox for to select the pages by easily.


Sunday, December 27, 2015

Use Calendar Control To Fill In Dates

Adding Date Easily With Calendar Control

       In our template, the date column is Column I (column 9).
If you select a cell in the Column I, the calendar will popup and when you click on the calendar the date will be placed in the active cell.

If you select a cell outside the Column I the calendar will disappear.


To appear and to hide of the "calendar control" is provided with Vba Codes :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Column
    Case Is = 9                         'Column No
        Sheets("Data1").Calendar1.Visible = True
        evnsatırı = ActiveWindow.ScrollRow
            For i = 1 To evnsatırı
            evngelbenimle = evngelbenimle + Cells(i, 1).Height
            Next i
 ActiveSheet.Shapes("Calendar1").Left = 490                'Left Margin
ActiveSheet.Shapes("Calendar1").Top = evngelbenimle - 12  'Top Margin
Case Else
Sheets("Data1").Calendar1.Visible = False
End Select
End Sub


Friday, December 25, 2015

Yearly Planner Spreadsheet - Agenda

A Yearly Calendar With Multi Line Cells To Store Your Notes

            Create Yearly Planning Spreadsheet İn Your Own Language.

When you click the button on the page , before inputbox opens to learn if you want to create a calendar which year. Wanted year's calendar is created. The language of your system is used when preparing  yearly .

            Twelve sheets are created for each month.
In column A in these pages,  days of the month are listed as day name and date of day.
For instance ; Mon 11.01.2016

            Hours of day are added as column heads.Data can be entered into cells as multiple lines  .
For this ,I added "WrapText = True" feature to codes.


Tuesday, December 22, 2015

Create A Picture From Cells

Creating Image From Selected Cells And Saving

        An image is created  from selected cell or cells. Generated images are saved to the  in the same  location with workbook.

Image names are checked and each recorded image is saved with a different name.
For example : myimage1.jpg,myimage2.jpg



myimage1.jpg

Sunday, December 20, 2015

Listbox Selection Methods And Copying Of Selected Data

Copy /Transfer Selected Rows Of Multiselect And Multi Column Listbox To Range Of Cells On Sheet 
- Single Item Selecting

- Multiple Items Selecting

- Multiple Selection By Pressing Ctrl And Shift

          In the page  searchable with the userform.

Items selected by the above methods are copied to other page (SeletctedData)

The first blank cell in  column A is selected while transferring .The border is drawn under of transfered data.



Thursday, December 17, 2015

Frame Effect

Frame Lengthening And Shortening Effect With Buttons

The loops were used  purpose of lengthening / shortening frame ,stopping operation.

For instance :

Private Sub CommandButton1_Click()
Dim i As Double
Cancel = False
i = Frame1.Height
    Do
        i = i + 0.015
        Frame1.Height = i
                 If Cancel Then
                Exit Do
            End If
        DoEvents
        Loop Until Frame1.Height >= 276
End Sub



Sunday, December 13, 2015

Create A New Book From Selected Pages

Create A New WorkBook From Selected Sheets On Userform -  Listbox Drag & Drop Event

       We put 3 list boxes on the userform.We added pages’ name of a book in the first listbox .
When clicked the page names in the listbox , content of the pages are displayed in another listbox (listbox3).

       To create a new book from selected pages, ago we drag page names from listbox(listbox1) to other listbox(listbox3) and we drop (listbox drag & drop) . Later when  we press the button , a new book is formed.



Also we created a popup menu that working on the listbox3  .


Tuesday, December 8, 2015

Simple Userform With Picture

Excel Simple Userform With Picture

         Simple userform with next and previous record displaying command buttons including picture displays.
Important point is that ,names of the pictures with names of people in column A are same.

If recording do not have picture ,"No Picture" (nopic.jpg) is displayed.


Monday, December 7, 2015

Price Quote Template

Price Quote Form Containing Image

          When product code is selected from combo boxes in Column B , product description, product image and product price are automatically assigned to the relevant cells.

I did not use Vba Codes in this template.Only I used formulas.



To delete data,  only  "-" you need to add into cell in column B.

Sunday, December 6, 2015

Excel Vba Game

To Reveal Colors Or Couples 

      Reveal colors or couples, to find pairs with as few tries as possible.Double click with  your mouse to reveal colors or couples within the black cell

Once found, pairs stay visible!

Level of the game can be selected with Level toolbar.So that ,new rows and columns  can be added to game.



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

Saturday, October 24, 2015

Excel Vba Hide & Unhide Columns

Hide & Unhide Columns With Userform

           A very useful application for columns management.
You can hide and unhide columns with userform .Columns are listed in the userform's listbox.
A checkbox is automatically assigned for each column in this listbox.

UserForm automatically start  when workbook opens.


ListBox Column Adding – Deleting

In The ListBox  Column Adding – Deleting |  Listbox Column Management

       Before in this template,we fill data   to the listbox from a page with the following codes :

ListBox1.ColumnWidths = "92;140;110;65;65;35;40;65;65;115;150;65"
ListBox1.ColumnCount = 12
ListBox1.List = Sheets("Data").Range("A2:L" & [A65536].End(3).Row).Value

    After ,  those columns that we choose   can be removed  with check boxes. It does not affect the data on this page.

Example codes of checkbox1 :

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
If CheckBox1.Value = True Then
For sat = 2 To Cells(65536, 1).End(xlUp).row
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, 1)
s = s + 1
Next
     Else
For i = 0 To ListBox1.ListCount - 1
        ListBox1.column(0, i) = Empty
     Next i
End If
Application.ScreenUpdating = True
End Sub



Friday, October 23, 2015

Excel Mouse Move Event

Mouse Move Event On Textbox
When the mouse over textbox on userform, font and background of the textbox is changing.


Wednesday, October 21, 2015

Converting Date To Month And Year

Convert Date To Month And Year With Vba

            Written date in column C, It is written as months and years into column A and column B.
Rows are colored differently for each month.



Monday, October 19, 2015

Label Management On Userform

Label Management On Userform With Buttons

         In this template ; font color, background color, font size, frame type etc. of label on the 
UserForm,with the spin buttons and buttons are set.