Wednesday, January 25, 2017

Excel Vba Games

Fun Games Created With Excel VBA Codes

There are three games in three separate workboks:
  1. Tetris
  2. Find Matches
  3. Rocket

Thursday, January 19, 2017

Parsing Data Into Multiple Worksheets With Drop-Down Lists

     
         In this study, the row is copied to the sheet (e.g On_hire sheet) according to the value selected from the drop-down list in column H, and the row is deleted.

The following steps have been taken for data parsing into multiple sheets :
- "Options" name is defined for Range("J1:J3")

- Drop-down lists is created in Column H using Data Validation - List Method. The name we defined is entered to the "Source" section.

- The macro codes are added to Worksheet_Change Function in Vbe window :
"Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Column = 8 Then
For i = 5 To Me.Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, "H").Value = "On hire" Then
        Rows(i).Copy
        Sheets("On_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Rows(i).Delete
        i = i - 1
    ElseIf Cells(i, "H").Value = "Off hire" Then
        Rows(i).Copy
        Sheets("Off_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Rows(i).Delete
        i = i - 1
ElseIf Cells(i, "H").Value = "On sales" Then
        Rows(i).Copy
        Sheets("On_sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Rows(i).Delete
        i = i - 1
End If
Next i
End If
End Sub"

excel parsing data

Saturday, January 14, 2017

Search The Matching Data Within Entire Workbook

          When the cell that to be searched is double-clicked , immediate search process is performed. Userform is loaded and  the matching cells  can be seen as page name and cell address on the userform.

 User can adapt this template to your own file. For this, follow the instructions below :
     - Open your own file
     - Press Alt+F11 keys and open VBE Window
     - In left side of VBE window, drag - drop the userform in the template to your own workbook
     - Copy the code in the ThisWorkBook field and paste it into this section of your own file.

search matching data

Monday, January 9, 2017

Employee Database With Images

         In the template, the employee information is entered into columns in the first sheet.On the other sheet,the recorded entries can be displayed in rows (vertically) in a column .
         We added a listbox, textbox, spinbutton to the report sheet. When this page is active, the names of the employees  are populated to listbox :
"lrow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
  ListBox1.List = Sheets("Data").Range("B2:B" & lrow).Value “


          If item of listbox is clicked, the employee's informations are displayed on rows in the sheet (e.g employee’s image,staff no, job title, date of employment).With spinbutton can be navigated between items of the listbox ,any value can be searched with textbox.

Thursday, January 5, 2017

Creating Table Of Contents

       
         If Excel workbook contains many sheets , a table of contents can be created to navigate easier between the sheets.
         This process can be done with macro .
This macro will create a new sheet at the start of the workbook named "Workbook_Index" when workbook opened. If this sheet already exists it will remove it and will rebuild. The macro will then list the names of all the sheets in the workbook and insert a hyperlink for each one.Also,can be returned to table of contents when the "Esc" key is pressed while on any sheet.
         The VBA code is displayed below. Copy and paste codes into the module of a workbook where you need to create a table of contents. 
Fort this ;
-Press Alt+F11 keys on any sheet to open Visual Basic Editor (VBE).
-Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
-Copy the VBA code below and paste it to the right pane of the VBA editor ("Module1" window).
-Confirm the changes , close the workbook and reopen it.

Macro code to add into module :
Sub auto_open()
Call create_index
Call return_index
End Sub
Sub Index_page()
    Sheets("Workbook_Index").Activate
End Sub
Sub create_index()
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 1
NewSheet ("Workbook_Index")
For Each Page In Worksheets
Sheets("Workbook_Index").Cells(k, 2).Select
Sheets("Workbook_Index").Cells(k, 1).Value = m & "-"

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Page.Name & "!A1", TextToDisplay:=Page.Name
k = k + 1
m = m + 1
Next Page
With Sheets("Workbook_Index")
.Columns(1).Interior.Color = RGB(215, 250, 198)
.Cells.RowHeight = 18
.Columns(1).Cells.HorizontalAlignment = xlHAlignRight
.Columns(2).Cells.HorizontalAlignment = xlHAlignLeft
.Columns(2).Interior.Color = RGB(255, 255, 163)
.Columns(1).EntireColumn.AutoFit
.Columns(2).EntireColumn.AutoFit
End With
End Sub
Function NewSheet(argCreateList)
    For Each Worksheet In ThisWorkbook.Worksheets
        If argCreateList = Worksheet.Name Then
             Application.DisplayAlerts = False
            Worksheet.Delete       ' if found - delete it
           
        End If
    Next Worksheet
    Worksheets.Add(Before:=Worksheets(1)).Name = argCreateList
End Function
Sub return_index()
Application.OnKey "{ESC}", "Index_page"
End Sub

excel table of contents

Wednesday, January 4, 2017

Excel Convert HEX To RGB

          Hexadecimal(Hex) color codes frequently are used in web designing (HTML-CSS etc.) .
In Excel, the RGB and HLS color codes  are used. Therefore, it is necessary to convert Hex values to RGB or HLS.
          For example,consider we have a hexadecimal code for a cell background color in Excel ; #FF8800 . This code corresponds to orange color.When we convert this code to rgb with userform that we created, the result is RGB(255,136,0). So, we can use the color code as Rgb in Excel and Vba.
For example : "Sheets("Page-1").Range("f11").Interior.Color = RGB(255, 136, 0)
                       TextBox1.BackColor = RGB(255, 136, 0)"

convert hex to rgb

Monday, January 2, 2017

Creating A Separate Sheet For Each Month With Macro


           When button is clicked, new sheets (12 sheets) are created for each month and added in workbook.
Namely months of the current year are ordered as pages.

Days of each month are listed in Column A as "dd.mm.yyyy". Today's date is selected from between dates.


creating month sheet macro