Create a text box without selecting it

[Bill Hamilton]

Hi,
I want to create a standard text box on a standard sheet with VBA without having to select it at any point. It will have a formula in it to point to a cell range which will change over the period the macro is running to give the users something to look at to stop them getting bored. I recorded the creation process for the box with the name and format I want and this is what it gave me (more or less):

Sub Macro3()
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 664, 90, 224, 86).Select
    Application.Goto Reference:="StatusBox"
    Selection.Formula = "=StatusBoxContents"
    With Selection.ShapeRange.TextFrame2.TextRange.Font
        .Name = "Tahoma"
        .Size = 11
        .Bold = True
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Weight = 1.5
    End With
End Sub

Could someone cleverer than I am convert that to a version which doesn't 'select' anything, please? I get really confused with shapes and their properties etc and everything I've tried using Withs has failed.

Hope someone can help.

 

[Peter_SSs]

Bill

See if this does what you want.

Sub AddShapeAndFormuala()
  With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 664, 90, 224, 86)
    .DrawingObject.Formula = "=StatusBox"
    With .Line
      .Visible = msoTrue
      .ForeColor.RGB = RGB(255, 0, 0)
      .Transparency = 0
      .Weight = 1.5
    End With
    With .TextFrame2.TextRange.Font
      .Name = "Tahoma"
      .Size = 11
      .Bold = True
    End With
  End With
End Sub

 

[Bill Hamilton]

Thank you Peter, perfect.

Well, not quite, but I sorted it. The range name you'd given in the Formula is actually the Name I wanted to give the Box, so it gave me a runtime error 1004 as that range doesn't exist (I suppose), and you'd missed off giving the box its Name.

So, I replaced the line after the AddTextbox line with
.DrawingObject.Formula = "=StatusBoxContents"
.Name = "StatusBox"
and it worked a treat.

Thanks again. I'd never in a million years come up with the DrawingObject.Formula construction, which is where I was sticking.

 

[Peter_SSs]

Thank you Peter, perfect.

Well, not quite, but I sorted it.

Yes, I wasn't quite sure what you were originally trying to do with StatusBox and StatusBoxContents

Anyway, glad it got you on the right path.

I'd never in a million years come up with the DrawingObject.Formula construction, which is where I was sticking.

Google solved it for me.