Create Hyperlinks for all Files in a Folder

Sub createHyperLink()
Dim I As Integer
Dim fl As File
Dim fldr As Folder
Dim sh As Worksheet
Dim fso As FileSystemObject
Dim path As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    path = .SelectedItems(1)
    Range("A1") = "File Name"
End With

I = 2

Set fso = New FileSystemObject
Set fldr = fso.GetFolder(path)
    For Each fl In fldr.Files
        ThisWorkbook.Sheets(1).Cells(I, 1) = fl.Name
            If ThisWorkbook.Sheets(1).Cells(I, 1) = fl.Name Then
                ThisWorkbook.Sheets(1).Cells(I, 1).Select
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fl.path
            End If
        I = I + 1
    Next fl

End Sub

Convert Excel File To PDF

Sub Excel2PDFConverter()
With Application.FileDialog(msoFileDialogFolderPicker)
              .Show
              Path = .SelectedItems(1)
End With
              ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & "\" & "exceltopdf"
End Sub

Sending mails without taking Outlook Reference

If your mailId is configured to Outlook Express; following code will send mails of each excel sheet

Sub Mail_every_Worksheet()

    Dim strDate As String
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("a1").Value Like "*@*" Then
            sh.Copy
            strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
            ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
                                & " " & strDate & ".xls"
            ActiveWorkbook.SendMail ActiveSheet.Range("a1").Value, _
                                    ActiveSheet.Range("b1").Value
            ActiveWorkbook.ChangeFileAccess xlReadOnly
         
            ActiveWorkbook.Close False
        End If
    Next sh
    Application.ScreenUpdating = True

End Sub

Calendar In VBA without ActiveX Control

Dim selecteddate As Date

Dim cbtarget As msforms.ComboBox, cbtarget1 As msforms.ComboBox

Dim myrng As Range

Private Sub ComboBox1_Change()

Dim dayscount

Dim tempvar

Dim dayName

Dim currentdate

Dim i As Integer, j As Integer

Dim cmdbutton1 As msforms.CommandButton

dayName = Format(DateSerial(year(Date), ComboBox1.ListIndex + 1, 1), "ddd")

Dim counter As Integer, daycounter As Integer

Dim cmdbutton() As Variant

dayscount = Day(DateSerial(year(Date), ComboBox1.ListIndex + 2, 0))

cmdbutton = Array("CommandButton1", "CommandButton2", "CommandButton3", "CommandButton4", "CommandButton5", "CommandButton6", "CommandButton7", "CommandButton8", "CommandButton9", "CommandButton10", "CommandButton11", "CommandButton12", "CommandButton13", "CommandButton14", "CommandButton15", "CommandButton16", "CommandButton17", "CommandButton18", "CommandButton19", "CommandButton20", "CommandButton21", "CommandButton22", "CommandButton23", "CommandButton24", "CommandButton25", "CommandButton26", "CommandButton27", "CommandButton28", "CommandButton29", "CommandButton30", "CommandButton31", "CommandButton32", "CommandButton33", "CommandButton34", "CommandButton35", "CommandButton36", "CommandButton37", "CommandButton38")

currentdate = Format(Date, "dd")

If ComboBox1.Value <> vbNullString Then

    If dayName = "Sun" Then

        counter = 0

        daycounter = 1

    ElseIf dayName = "Mon" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        counter = 1

        daycounter = 1

    ElseIf dayName = "Tue" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        counter = 2

        daycounter = 1

    ElseIf dayName = "Wed" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        counter = 3

        daycounter = 1

    ElseIf dayName = "Thu" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        Calendar.Controls(cmdbutton(3)).Visible = False

        counter = 4

        daycounter = 1

    ElseIf dayName = "Fri" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        Calendar.Controls(cmdbutton(3)).Visible = False

        Calendar.Controls(cmdbutton(4)).Visible = False

        counter = 5

        daycounter = 1

    ElseIf dayName = "Sat" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        Calendar.Controls(cmdbutton(3)).Visible = False

        Calendar.Controls(cmdbutton(4)).Visible = False

        Calendar.Controls(cmdbutton(5)).Visible = False

        counter = 6

        daycounter = 1

        

    End If

    For i = counter To (counter + dayscount) - 1

        

        Calendar.Controls(cmdbutton(i)).Visible = True

        Calendar.Controls(cmdbutton(i)).BackColor = RGB(135, 206, 250)

        Calendar.Controls(cmdbutton(i)).ForeColor = RGB(102, 0, 51)

        Calendar.Controls(cmdbutton(i)).FontBold = True

        Calendar.Controls(cmdbutton(i)).Caption = daycounter

        If (daycounter = currentdate) And ((ComboBox1.ListIndex + 1) = Month(Date)) Then

            

            Calendar.Controls(cmdbutton(i)).BackColor = RGB(255, 255, 204)

            

        End If

        daycounter = daycounter + 1

    Next i

    Calendar.Controls(cmdbutton(0)).ForeColor = 255

    Calendar.Controls(cmdbutton(7)).ForeColor = 255

    Calendar.Controls(cmdbutton(14)).ForeColor = 255

    Calendar.Controls(cmdbutton(21)).ForeColor = 255

    Calendar.Controls(cmdbutton(28)).ForeColor = 255

    Calendar.Controls(cmdbutton(35)).ForeColor = 255

    For j = (counter + dayscount) To 37

        Calendar.Controls(cmdbutton(j)).Visible = False

    Next j

End If

End Sub

Private Sub CommandButton1_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton1.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton2_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton2.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton3_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton3.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton4_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton4.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton5_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton5.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton6_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton6.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton7_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton7.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton8_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton8.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton9_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton9.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton10_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton10.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton11_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton11.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton12_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton12.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton13_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton13.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton14_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton14.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton15_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton15.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton16_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton16.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton17_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton17.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton18_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton18.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton19_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton19.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton20_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton20.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton21_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton21.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton22_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton22.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton23_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton23.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton24_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton24.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton25_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton25.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton26_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton26.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton27_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton27.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton28_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton28.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton29_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton29.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton30_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton30.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton31_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton31.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton32_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton32.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton33_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton33.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton34_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton34.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton35_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton35.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton36_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton36.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton37_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton377.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton38_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton38.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub Label3_Click()

End Sub

Private Sub UserForm_Initialize()

Set myrng = ThisWorkbook.Sheets(1).Range("L1:L12")

Calendar.BackColor = RGB(188, 143, 143)

Set cbtarget = Me.ComboBox1

Set cbtarget1 = Me.ComboBox2

cbtarget.List = myrng.Cells.Value

cbtarget1.Value = year(Date)

cbtarget.Value = Format(Date, "mmm")

Label3.ForeColor = 255

End Sub

Move data from Row to Column

When there is a huge data in a single column; we may need to migrate some data to next column using page break. VBA code for this utility:

Option Explicit
Dim sht As Worksheet, myrng As Range, myrng1 As Range, count As Integer, i As Long
Dim myrng2 As Range, rowcount As Long
Sub moveRowtoColumn()
On Error Resume Next
Set sht = ThisWorkbook.Sheets(1)
Set myrng = sht.UsedRange.Columns(1)
count = sht.HPageBreaks.count
i = 0

For i = 1 To count
 
    Set myrng1 = sht.HPageBreaks(i).Location
    Set myrng2 = sht.HPageBreaks(i + 1).Location.Offset(-1, 0)
        If myrng2 Is Nothing Then
            Range(myrng1, myrng.Cells(65500, 1).End(xlUp)).Copy Destination:=sht.Cells(1, i + 1)
        Else
            Range(myrng1, myrng2).Copy Destination:=sht.Cells(1, i + 1)
        End If
     
    Set myrng1 = Nothing
    Set myrng2 = Nothing
Next

sht.Range(sht.HPageBreaks(1).Location.Address, myrng.Cells(65500, 1).End(xlUp).Address).Delete
    Set myrng = Nothing
End Sub

Floating Button in VBA

VBA code for creating floating button in excel sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Target.Column < 2 And Target.Row < 2) Then Exit Sub
With ActiveSheet.Shapes("CommandButton1")
    .Top = Target.Offset(, 0).Top
    .Left = Target.Offset(, 0).Left
End With
End Sub