Animation Of The Colors With Excel VBA Codes




           Excel animation with voice and music prepared with Excel VBA codes to learn Spanish colors.

When clicked on the color circles, the animation of clicked color starts A 3D cylinder was selected as the animation item.At the end of the animation, the name of the color is played and a child laughing effect is played using playing function. Spanish colours were used as example colors in the template.

 The function that generates moving :
Sub basla()
 m = 0
    For i = 1 To 90
      Selection.ShapeRange.ThreeD.RotationY = m
      m = m - 1
    DoEvents
    Next i
        For i = 1 To 180
      Selection.ShapeRange.ThreeD.RotationY = m
      m = m + 1
      DoEvents
    Next i
        For i = 1 To 90
      Selection.ShapeRange.ThreeD.RotationY = m
      m = m - 1
      DoEvents
    Next i
        For i = 1 To 90
      Selection.ShapeRange.ThreeD.RotationX = m
      m = m - 1
      DoEvents
    Next i
       For i = 1 To 180
      Selection.ShapeRange.ThreeD.RotationX = m
      m = m + 1
      DoEvents
    Next i
        For i = 1 To 90
      Selection.ShapeRange.ThreeD.RotationX = m
      m = m - 1
      DoEvents
    Next i
    End Sub



Read more ...

Excel VBA Dictionary (Catalog) Form

Excel VBA Dictionary Userform




        It can be used as an application such as dictionary, index.
For example, we entered the names of the vitamins to column A and entered descriptions of vitamins to column B.
The data in column A of the sheet is filled to the combobox on the userform.

The value corresponding to the item selected from the combobox is fetched from the column B and displayed on label under the combobox.

The used codes to increase the width of the UserForm:
Private Sub CommandButton4_Click()
Dim X, d, yuk As Integer
If CommandButton4.Caption = "8" And Data_Form.Width = 410 Then
For d = 1 To 158
DoEvents
yuk = 410
Data_Form.Width = yuk - d
Next
ComboBoxTopics.Enabled = True
ComboBoxTopics.ListIndex = 0
CommandButton4.Caption = "9"
Exit Sub
End If
If CommandButton4.Caption = "9" And Data_Form.Width = 252 Then
For X = 1 To 158
DoEvents
yuk = 252
Data_Form.Width = yuk + X
Next
ComboBoxTopics.Enabled = False
CommandButton4.Caption = "8"
End If
End Sub


Read more ...

Excel VBA Animation Examples

Excel Animations That Created With VBA Codes     


         Simple and instructive animations for those who want to learn the Excel programming. Macro codes of the animations can be viewed in the workbooks.


⇨ Generally ,the shapes in the worksheet are moved with Vba codes and loops.

excel animations


excel vba animation sample
Let's review an VBA animation sample :


🔊 This Excel animation includes an .wav file to play audio during action.


excel animation with audio

The codes that provide action when the start button is clicked:
Sub Baslat()
SesDosyasi = ThisWorkbook.Path & Application.PathSeparator & "ses1.wav"
Dim sh As Shape
Dim s1 As Worksheet
Dim r As Range
Dim konumX As Double
Dim konumY As Double

Dur = False

Set s1 = ThisWorkbook.Worksheets("ANIMA")
Set sh = s1.Shapes("excelvba.net")
Set r = s1.Range("F7")
If Not IsNumeric(s1.Range("HIZ")) Then Hiz = 10 Else Hiz = s1.Range("HIZ")
If Hiz <= 0 Or Hiz > 50 Then
Hiz = 1
s1.Range("HIZ") = 10
End If
If Not IsNumeric(s1.Range("ACI")) Then Aci = 0 Else Aci = s1.Range("ACI")
If Aci < 0 Or Aci > 360 Then
Aci = 0
s1.Range("ACI") = 0
End If

radyan = Aci * pi / 180
konumX = sh.Left
konumY = sh.Top
Do
'Calculate position konumX = konumX + 3 * Cos(radyan)
konumY = konumY + 3 * Sin(radyan)

'Did the shape hit the right edge?
If (konumX + sh.Width >= r.Left + r.Width) Then
konumX = r.Left + r.Width - sh.Width
radyan = pi - radyan
sh.Adjustments(1) = 0.718
sndPlaysound SesDosyasi, 1
End If

'Did the shape hit the left edge?
If (konumX <= r.Left) Then
konumX = r.Left
radyan = pi - radyan
sh.Adjustments(1) = 0.718
sndPlaysound SesDosyasi, 1
End If

'Did the shape hit the up ?
If (konumY <= r.Top) Then
konumY = r.Top
radyan = radyan * -1
sh.Adjustments(1) = 0.718
sndPlaysound SesDosyasi, 1
End If

'Did the shape hit the bottom ?
If (konumY + sh.Height >= r.Top + r.Height) Then
konumY = r.Top + r.Height - sh.Width
radyan = -1 * radyan
sh.Adjustments(1) = 0.718
sndPlaysound SesDosyasi, 1
End If

sh.Left = konumX
sh.Top = konumY
sh.Rotation = sh.Rotation + 3
sh.Adjustments(1) = sh.Adjustments(1) + 0.002
Bekle (50 / Hiz)
Loop Until Dur
End Sub

Lines starting with apostrophes ( ' ) in VBA codes are comments.
excel vba animation action excel movement


Read more ...

Display The Pictures On Userform

Display The Pictures On Userform & Picture Management With Userform



         In this tutorial, with userform, you can add and delete new images to the workbook folder.
As an example, we added the workbook and some images to a folder. When the userform is loaded, the names of images with .jpg extension in the folder are sorted on the listbox. 
The image selected from the listbox is displayed on the “image control”. 

By clicking the buttons on the userform you can add new pictures to the folder and delete them.

A different example of Excel VBA Image Control.


Read more ...

Play Musical Notes In Excel

Excel VBA Play Music Notes


excel play music notes

           Musical notes (piano notes) can be played with buttons and functional keys in Excel.

Ago, we added buttons to the worksheet .Then ,we assigned the macros to these buttons to play the music notes(.wav files) that are in the same place as the workbook. Example ;
Private Sub CommandButton2_Click()
  Call sndPlaySound32(ThisWorkbook.Path & "\a1.wav", 0)
  Range("F2").Activate
  End Sub

 If desired music notes can be played with function keys F1, F2 etc. We used the following codes for this process .


Read more ...

Find Data Between Two Dates In Excel VBA

Excel VBA Find Data Between Two Dates


excel filter between two dates



          Records in between two specific dates easily can be filtered using drop-down lists .
Ago ,drop-down lists (combo boxes) were filled with unique values as ascending order using ADO connection.  Thus, it is easier to choose between the dates on the worksheet. The used codes for this :
Set con = CreateObject("adodb.connection")
Sheets("Page1").ComboBox1.Clear
Sheets("Page1").ComboBox2.Clear
    #If VBA7 And Win64 Then
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=yes"""
    #Else
con.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=yes"""
    #End If     
Set rs = CreateObject("adodb.recordset")
sorgu = "select Date from [Page1$] group by Date"
rs.Open sorgu, con, 1, 1
While Not rs.EOF
Sheets("Page1").ComboBox1.AddItem VBA.Format(rs("Date").Value, "dd.mm.yyyy")
Sheets("Page1").ComboBox2.AddItem VBA.Format(rs("Date").Value, "dd.mm.yyyy")
rs.movenext
Wend

Then ,the data between the two dates selected from combo boxes is filtered through Vba codes :
 lngStart = VBA.CDate(Sheets("Page1").ComboBox1) 'assume this is the start date
    lngEnd = VBA.CDate(Sheets("Page1").ComboBox2) 'assume this is the end date
    If lngStart > lngEnd Then
    MsgBox "The Start Date Can Not Be Bigger Than The End Date.", vbCritical, ""
    Exit Sub
    End If
    Sheets("Page1").Range("B1").AutoFilter field:=1, _
    Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd

The filtered data can be copied to other sheet if it wished.

Read more ...

Create Simple Dynamic(Dependent) Drop Down Lists In Excel

Excel Dynamic Drop Down Lists


         In worksheet, we can create dynamic drop down lists with the Data Validation feature and the Indirect function. 
In sample sheet,we have a table of five columns that indicate five types of foodstuff: fruit, food, meat,vegetable and drink and below them are the specific food name :
excel dependent drop down list
         We need to create one drop down list that contains the foodstuff, such as fruit, food, vegetable,meat and drink . The second drop-down would have the specific food name. If we select meat item from first drop-down, the second drop-down will show beef, mutton, chicken, port, fish and veal.

To do this, please apply the following steps:
First, we need to create range names for these columns and the first categories row.
 Let's create a range name for the categories,for this the first row, we selected the A1:E1, and typed the range name Foodstuff into the Name Box, then pressed Enter key.
excel define name
 Then we need to name the range for each of the columns  as shown below:
excel dependent drop down list
 Now we can create the first drop down list, we selected a blank cell or a column that we want to apply this drop down list (I5 cell is selected), and then we clicked Data > Data Validation .In the Data Validation dialog box, we clicked Settings tab, we chose List from the Allow drop down list, and entered this formula =Foodstuff into the Source box.
Our first drop down list have been created.

 Then we can create the second drop down list,we selected J5 cell, and click Data > Data Validation again, in the Data Validation dialog box, we clicked Settings tab, we chose List from the Allow drop down list, and entered this formula =indirect($I$5) into the Source box.

Our dependent drop down list have been created successfully.
If user choose one type of the foodstuff, the corresponding cell will only display its specific food name.


Read more ...

Creating Invoice And Entering Data Quickly Through Userforms

Excel Automated Invoice With Userforms




           In this invoice template ,the userforms that contains textbox,listbox and buttons are used to enter quickly data of customers and products:

✔️ Addition and multiplication operations in column G are performed through formulas (=E21*F21,        =SUM(G21:G35) etc.) The used formulas in worksheet are protected by the following codes :
      Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$G$21" Then
      Target.Formula = "=E21*F21"
      ElseIf Target.Address = "$G$22" Then
      Target.Formula = "=E22*F22"
       ElseIf Target.Address = "$G$23" Then
      Target.Formula = "=E23*F23"
       ElseIf Target.Address = "$G$24" Then
      Target.Formula = "=E24*F24"
       ElseIf Target.Address = "$G$25" Then
      Target.Formula = "=E25*F25"
       ElseIf Target.Address = "$G$26" Then
      Target.Formula = "=E26*F26"
       ElseIf Target.Address = "$G$27" Then
      Target.Formula = "=E27*F27"
       ElseIf Target.Address = "$G$28" Then
      Target.Formula = "=E28*F28"
       ElseIf Target.Address = "$G$29" Then
      Target.Formula = "=E29*F292"
       ElseIf Target.Address = "$G$30" Then
      Target.Formula = "=E30*F30"
       ElseIf Target.Address = "$G$31" Then
      Target.Formula = "=E31*F31"
       ElseIf Target.Address = "$G$32" Then
      Target.Formula = "=E32*F32"
       ElseIf Target.Address = "$G$33" Then
      Target.Formula = "=E33*F33"
       ElseIf Target.Address = "$G$34" Then
      Target.Formula = "=E34*F34"
       ElseIf Target.Address = "$G$35" Then
      Target.Formula = "=E35*F35"
       ElseIf Target.Address = "$G$36" Then
      Target.Formula = "=SUM(G21:G35)"
       ElseIf Target.Address = "$G$41" Then
      Target.Formula = "=SUM(G36:G39)"
      End If
      End Sub

✔️ The created invoice can be copied to selected record sheet.


Read more ...

Excel VBA Games : Find Matches | Rocket Throwing | Tetris

Fun Games Created With Excel VBA Codes


        Simple and fun VBA games that can be played inside the Excel worksheet.
There are three games in three separate workbooks:

  1. Find Matches : In the game , you need to find the same colored cells by double-clicking on the cells.

  1. Rocket Throwing : Press "Enter" key, the rocket moves, and passes through the barriers with up-down keys and completes its movement smoothly.

  1. Tetris : The classic game was brought to life with VBA codes.



Read more ...

Excel Convert HEX To RGB

Excel VBA Convert HEX To RGB With Userform


          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)

excel hex to rgb


convert hex to rgb

Read more ...

Creating A Separate Sheet For Each Month With Macro

Excel VBA Create The Separate Sheet For Each Month

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

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

When the "Delete Sheets" button is pressed, the created 12 sheets are deleted.

vba create sheet for each month

The VBA codes we use to create the months:
Sub CreateMonths()
   Dim lDay As Long
   Dim iWks As Integer, iDay As Integer
   On Error Resume Next
   For iWks = 1 To 12
      Worksheets.Add after:=Worksheets(Worksheets.Count)
      ActiveSheet.Name = Format(DateSerial(1, iWks, 1), "mmmm")
       ActiveWorkbook.Worksheets.Application.Columns("A:A").ColumnWidth = 11
   'ActiveWorkbook.Worksheets.Application.Columns("A:A").EntireColumn.AutoFit
      For lDay = DateSerial(Year(Date), iWks, 1) To DateSerial(Year(Date), iWks + 1, 0)
         iDay = iDay + 1
         Cells(iDay, 1).Value = DateSerial(Year(Date), iWks, iDay)
      Next lDay
      iDay = 0
   Next iWks
  GotoToDay
  End Sub

Sub GotoToDay()
   Dim iRow As Integer
   Worksheets(Month(Date) + 1).Select
   iRow = WorksheetFunction.Match(CDbl(Date), Columns(1), 0)
   Cells(iRow, 1).Select
End Sub

In the above codes, the GotoToDay procedure is run to select today's date.

creating month sheet macro

Read more ...