vb10 adding a label in excell

RGuillermo used Ask the Experts™
Hello Experts,
I got this sentence from a previous question.

the definition is accepted by vb10

Dim sh As Shape = objsheet.Shapes.AddLabel(msoTextOrientationHorizontal, 62.25, 97.5, 72, 72)

after that i need to change text, fonsize, fontcolor, move position.
How can I manipulate the object created lines above??

So far the following sentence is not accepted:
sh.textframe.characters.text = "AAAAAAA"

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Your code works just fine, with slight modification.  What do you mean not accepted?

here's the modified code:
Sub test()
Dim sh As Shape
Dim objSheet As Worksheet

    Set objSheet = Sheet1
    Set sh = objSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 62.25, 97.5, 72, 72)
    sh.TextFrame.Characters.Text = "AAAAAAA"
End Sub

Open in new window

See attached demonstration.

Most Valuable Expert 2012
Top Expert 2012
I built a simple VB.Net Windows Forms app, adding a reference to the COM object - Microsoft xx.x Object Library, then added a button to spawn an instance of Excel, add a workbook, then entered the code to add shape and modify the characteristics of the text box you're wanting to add, also re-positioning the text box, as follows:

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As Excel.Application
        Dim objWkb As Excel.Workbook
        Dim objSheet As Excel.Worksheet

        'instantiate Excel
        oExcel = New Excel.Application

        'make it visible
        oExcel.Visible = True

        'add a new workbook
        objWkb = oExcel.Workbooks.Add()
        objSheet = CType(objWkb.ActiveSheet, Excel.Worksheet)

        'create shape at active sheet
        Dim sh As Excel.Shape = objSheet.Shapes.AddLabel(CType(1, Microsoft.Office.Core.MsoTextOrientation), 62.25, 97.5, 72, 72) '1 for Horizontal Orientation

        'change some attributes of the shape
        With sh.TextFrame.Characters
            .Text = "AAAAAA"
            .Font.Size = 21
            .Font.Color = 255 'make the font red
        End With

        'move the textbox to align with cell G10 on the active sheet
        With objSheet.Range("G10")
            sh.Top = CSng(.Top)
            sh.Left = CSng(.Left)
        End With

    End Sub
End Class

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Do you get compile time errors? Or warnings?
RGuillermoProject Manager


Works Perfect! Thank you very much!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial