Imports OfficeCore = Microsoft.Office.Core
Public Class ThisWorkbook

    Private Sub ThisWorkbook_Startup() Handles Me.Startup
        'Comment1()
        'Comment2()
        'comment3_Shape()
        'Comment4_Remove()
        TestCommnetAutoShape()
    End Sub

    Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown

    End Sub

    Sub ChangeShape(Rng As Excel.Range)
        Rng.Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShape16pointStar
    End Sub

    Sub Comment1()
        Dim Cell1 As Excel.Range
        Cell1 = Globals.Sheet1.Range("D3")
        Cell1.AddComment("Test Comment1")
        Cell1.Comment.Shape.AutoShapeType _
            = OfficeCore.MsoAutoShapeType.msoShape16pointStar
        If Cell1.Comment Is Nothing Then
            Cell1.AddComment("Test Comment1")
        Else
            Cell1.Comment.Text("Test Comment2")
        End If
    End Sub

    Sub Comment2()
        Dim Cell1 As Excel.Range
        Cell1 = Globals.Sheet1.Range("D4")
        Cell1.AddComment("Test Comment1")
        Dim Shp1 As Excel.Shape = Cell1.Comment.Shape
        Shp1.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShape16pointStar
        Shp1.Fill.ForeColor.RGB = RGB(0, 200, 0)
    End Sub

    Sub comment3_Shape()
        Dim cell2 As Excel.Range = Globals.Sheet1.Range("E5")
        cell2.Value2 = "Test"
        cell2.AddComment("This is a comment." & _
                         "This is a comment." & _
                         "This is a comment." & _
                         "This is a comment." & _
                         "This is a comment.")
        With cell2.Comment.Shape
            .AutoShapeType = _
                Microsoft.Office.Core. _
                MsoAutoShapeType.msoShapeHorizontalScroll
            .Height = 150
            .Width = 150
            .Fill.ForeColor.RGB = RGB(255, 200, 255)
            .TextFrame.Characters.Font.Name = "Arial"
            .TextFrame.Characters.Font.Size = 12
            .TextFrame.Characters.Font.ColorIndex = 11
            .TextFrame.MarginBottom = Application.InchesToPoints(0.5)
            .TextFrame.MarginLeft = Application.InchesToPoints(0.5)
            .TextFrame.MarginRight = Application.InchesToPoints(0.5)
            .TextFrame.MarginTop = Application.InchesToPoints(0.5)
            .Line.ForeColor.RGB = RGB(255, 0, 0)
            .Line.Weight = 1
            .Line.DashStyle = _
                Microsoft.Office.Core. _
                MsoLineDashStyle.msoLineRoundDot
        End With
    End Sub

    Sub Comment4_Remove()
        Globals.Sheet1.Range("D4").Comment.Delete()
    End Sub

    Private Sub TestCommnetAutoShape()
        Dim thisWb As Excel.Workbook = Me.Application.ActiveWorkbook
        Dim SH As Excel.Worksheet = thisWb.Sheets(1)
        For i = 0 To 93
            SH.Range("B1").Offset(i, 0).AddComment("Comment")
        Next
        Dim RNG As Excel.Range
        RNG = SH.Range("B1")
        RNG.Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShape16pointStar
        RNG.Offset(1, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeBevel
        RNG.Offset(2, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeBlockArc
        RNG.Offset(3, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeCan
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeChartPlus
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeChartStar
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeChartX
        RNG.Offset(4, 0).Comment.Shape.AutoShapeType = OfficeCore.MsoAutoShapeType.msoShapeChevron
        'RNG.Offset(5, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeChord
        RNG.Offset(6, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCircularArrow
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCloud
        RNG.Offset(7, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCloudCallout
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCorner
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCornerTabs
        RNG.Offset(8, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCross
        RNG.Offset(9, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCube
        RNG.Offset(10, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCurvedDownArrow
        RNG.Offset(11, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCurvedDownRibbon
        RNG.Offset(12, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCurvedLeftArrow
        RNG.Offset(13, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCurvedRightArrow
        RNG.Offset(14, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeCurvedUpRibbon
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDecagon
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDiagonalStripe
        RNG.Offset(15, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDiamond
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDodecagon
        RNG.Offset(16, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDonut
        RNG.Offset(17, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDoubleBrace
        RNG.Offset(18, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDoubleBracket
        RNG.Offset(19, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDoubleWave
        RNG.Offset(20, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDownArrow
        RNG.Offset(21, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDownArrowCallout
        RNG.Offset(22, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeDownRibbon
        RNG.Offset(23, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeExplosion1
        RNG.Offset(24, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeExplosion2
        RNG.Offset(25, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartAlternateProcess
        RNG.Offset(26, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartCard
        RNG.Offset(27, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartCollate
        RNG.Offset(28, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartConnector
        RNG.Offset(29, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartData
        RNG.Offset(30, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartDecision
        RNG.Offset(31, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartDelay
        RNG.Offset(32, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartDirectAccessStorage
        RNG.Offset(33, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartDisplay
        RNG.Offset(34, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartDocument
        RNG.Offset(35, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartExtract
        RNG.Offset(36, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartInternalStorage
        RNG.Offset(37, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartMagneticDisk
        RNG.Offset(38, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartManualInput
        RNG.Offset(39, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartManualOperation
        RNG.Offset(40, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartMerge
        RNG.Offset(41, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartMultidocument
        RNG.Offset(42, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartOffpageConnector
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartOfflineStorage
        RNG.Offset(43, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartOr
        RNG.Offset(44, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartPredefinedProcess
        RNG.Offset(45, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartPreparation
        RNG.Offset(46, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartProcess
        RNG.Offset(47, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartPunchedTape
        RNG.Offset(48, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartSequentialAccessStorage
        RNG.Offset(49, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartSort
        RNG.Offset(50, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartStoredData
        RNG.Offset(51, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartSummingJunction
        RNG.Offset(52, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFlowchartTerminator
        RNG.Offset(53, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFoldedCorner
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFrame
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeFunnel
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeGear6
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeGear9
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeHalfFrame
        RNG.Offset(54, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeHeart
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeHeptagon
        RNG.Offset(55, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeHexagon
        RNG.Offset(56, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeHorizontalScroll
        RNG.Offset(57, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeIsoscelesTriangle
        RNG.Offset(58, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftArrow
        RNG.Offset(59, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftArrowCallout
        RNG.Offset(60, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftBrace
        RNG.Offset(61, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftBracket
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftCircularArrow
        RNG.Offset(62, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftRightArrow
        RNG.Offset(63, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftRightArrowCallout
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftRightCircularArrow
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftRightRibbon
        RNG.Offset(64, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftRightUpArrow
        RNG.Offset(65, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLeftUpArrow
        RNG.Offset(66, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLightningBolt
        RNG.Offset(67, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout1
        RNG.Offset(68, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout1AccentBar
        RNG.Offset(69, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout1BorderandAccentBar
        RNG.Offset(70, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout1NoBorder
        RNG.Offset(71, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout2
        RNG.Offset(72, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout2AccentBar
        RNG.Offset(73, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout2BorderandAccentBar
        RNG.Offset(74, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout2NoBorder
        RNG.Offset(75, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout3
        RNG.Offset(76, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout3AccentBar
        RNG.Offset(77, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout3BorderandAccentBar
        RNG.Offset(78, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout3NoBorder
        RNG.Offset(79, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout4
        RNG.Offset(80, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout4AccentBar
        RNG.Offset(81, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout4BorderandAccentBar
        RNG.Offset(82, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineCallout4NoBorder
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeLineInverse
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMathDivide
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMathEqual
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMathMinus
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMathMultiply
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMathNotEqual
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMathPlus
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMixed
        RNG.Offset(83, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeMoon
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeNonIsoscelesTrapezoid
        RNG.Offset(84, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeNoSymbol
        RNG.Offset(85, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeNotchedRightArrow
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeNotPrimitive
        RNG.Offset(86, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeOctagon
        RNG.Offset(87, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeOval
        RNG.Offset(88, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeOvalCallout
        RNG.Offset(89, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeParallelogram
        RNG.Offset(90, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapePentagon
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapePie
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapePieWedge
        RNG.Offset(91, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapePlaque
        'RNG.Offset(1, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapePlaqueTabs
        RNG.Offset(92, 0).Comment.Shape.AutoShapeType = Microsoft.Office.Core.MsoAutoShapeType.msoShapeQuadArrow

        For i = 0 To 93
            SH.Range("B1").Offset(i, 0).Value2 = SH.Range("B1").Offset(i, 0).Comment.Shape.AutoShapeType.ToString
            SH.Range("B1").Offset(i, 0).EntireColumn.AutoFit()
            SH.Range("B1").Offset(i, 0).EntireRow.AutoFit()
        Next
    End Sub

End Class
