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.


Sunday, October 18, 2015

Filling Data Automatically In Cells

Fill Data Automatically In Cells With Macro

      We used  Column B and C as sample .

When data is written into any row of column B and column C, in these columns data are filled up to the most recently used  row in sheet .





Thursday, October 15, 2015

Music Instruments in Excel

Play Music Instruments In Excel By Offline

         There are three musical instruments in the this template.The piano,the guitar,the drums.
You can additionally register yourself music.Lovely a template and it's sound effects are very good.


Adding Button Into All Pages

Adding Button Into All Pages With Macro

       When we pressed button in the sheet1 to run macro, a button is added to all pages the same coordinates.
We assigned  for this buttons  "task of userform opening" .


Saturday, October 10, 2015

Excel Grouping Data and Coloring

Sorting Data And Fill Background With Different Colors

        Ago in this template,we sorted data by ascending order according to column A.
After we painted  background color of the sorted cells with different colors.Colors are randomly selected by macro codes .

Table can be more better analyzed with different colored data .

















Thursday, October 8, 2015

Cell Magnifier

Excel Cell Magnifier

        Font of selected cell is growing in this template.
When cell selection  is canceled , font of cell is set as the default .


Wednesday, October 7, 2015

Excel Chronometer (Stopwatch)

Excel Chronometer Example

         A nice Excel chronometer form.
The stopwatch is running on the userform.The stopwatch can be stopped and value in screen can be saved.



Tuesday, October 6, 2015

Excel Vba Autofilter Using UserForm

Excel Vba Autofilter Using The UserForm

         Autofilter is done using userform (with check boxes and combo boxes) in this template.
Simple and easy to understand example.


Sunday, October 4, 2015

Vertical Filtering

Excel : Ranking The Filtering Results  İnto Column From Up To Down

            Unlike other templates in this template ,filtering is done with text boxes to "Filtering" page from "Data" page.
The found results are listed in "Filtering" page's columns E (E2:E12) and F (F2:F12)

Saturday, October 3, 2015

Parsing The Data To Pages

Deploy Data To Pages According To Values (Ok Or Cancel) On The Column

            If  the value of the data in the E column is "OK" ,row is transfered to "Done" page . 
If  the value of the data in the E column is "Cancel" ,row is transfered to "Cancelled" page .


Friday, October 2, 2015

Filtering Data By Multiple Criteria On Userform

Filtering Data By Multiple Criteria
             An advanced example about filtering.
We can filter on userform with combo boxes according to multiple criteria in this template .
Also can be filtered between columns according to many operators (=, >, >=, <, <=) comparison.