vb10 adding a label in excell

RGuillermo
RGuillermo used Ask the Experts™
on
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"

regards,
Comment
Watch Question

Do more with

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

Commented:
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.

Dave
testShape-r1.xls
Most Valuable Expert 2012
Top Expert 2012
Commented:
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


Dave
Most Valuable Expert 2012
Top Expert 2014

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

Author

Commented:
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