Automatic Object Naming

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
I've been having problems where I make an object on a sheet and its given an automatic name...

For example I create a text box using a macro, it will be named "text box 1" I get the problem when I macro grabs text box 1 and moves it but after I've run the macro a few times text box 1 is now text box 10 or something.

How can I add a line of code that when the macro runs and creats a text box it names it something that I set that will be consistant.

Code:
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 47.25, 100.5, _
        209.25, 84.75).Select
    Selection.Characters.Text = "Hello world"
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.ShapeRange.IncrementLeft 18#
    Selection.ShapeRange.IncrementTop 13.5
    ActiveSheet.Shapes("Text Box 1").Select

So Add a line saying something like

ActiveSheet.Shapes.AddTextbox.name = "my new text box name"

As you can see form the above code the last line says .

ActiveSheet.Shapes("Text Box 1").Select

but if that macro was run twice it wouldnt be text box 1 any more...

Hope that makes sence sorry its a bit long winded!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use an object variable to keep track of your shape:

Code:
Sub Test()
    Dim Sh As Shape
    Set Sh = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 47.25, 100.5, _
        209.25, 84.75)
    With Sh
        .Name = "MyShape"
        With .TextFrame.Characters
            .Text = "Hello world"
            With .Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
        End With
        .Select
    End With
    With Selection.ShapeRange
        .IncrementLeft 18#
        .IncrementTop 13.5
    End With
End Sub
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Great stuff cheers.

Can you explain to me the:

Code:
Dim Sh As Shape 
    Set Sh = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 47.25, 100.5, _ 
        209.25, 84.75) 
    With Sh 
        .Name = "MyShape"

I understand what the .name does but what does Dim sh as shape, set Sh and with Sh do?

Many thanks
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The first line declares a variable named Sh as type Shape.

The AddTextbox method returns a TextBox (Shape) object. The Set keyword is used to assign the returned Shape to the object variable.

Thereafter you can use the object variable in place of the actual object.
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Ah I see thats quite interesting.

So why cant we just do.

Code:
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 47.25, 100.5, _ 
        209.25, 84.75) 
        .Name = "MyShape"
         With .TextFrame.Characters 
            .Text = "Hello world" 
            With .Font 
                .Name = "Arial" 
                .FontStyle = "Regular" 
                .Size = 10 
                .Strikethrough = False 
                .Superscript = False 
                .Subscript = False 
                .OutlineFont = False 
                .Shadow = False 
                .Underline = xlUnderlineStyleNone 
                .ColorIndex = xlAutomatic 
            End With 
        End With 
        .Select 
    End With 
    With Selection.ShapeRange 
        .IncrementLeft 18# 
        .IncrementTop 13.5 
    End With 
End Sub

Would that not create a shape with the name as "MyShape"?

Cheers.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
No it wouldn't. In your original code you applied the Select method to the shape returned by the AddTextbox method, then used the Selection object. The first line above will casue a compile error, because the arguments for the AddText method are in parentheses, and VBA expects the result to be assigned to a variable or passed to another method.

Using the result in a With clause works though:

Code:
Sub Test()
    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 47.25, 100.5, _
            209.25, 84.75)
        .Name = "MyShape"
         With .TextFrame.Characters
            .Text = "Hello world"
            With .Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
        End With
        .Select
    End With
    With Selection.ShapeRange
        .IncrementLeft 18#
        .IncrementTop 13.5
    End With
End Sub
 

Forum statistics

Threads
1,136,349
Messages
5,675,243
Members
419,556
Latest member
rdecker12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top