Assigning name to button

[Grant Reid]

Hi

I have recorded a macro that creates a button, assigns a macro and positions
the button. In my spreadsheet application it is often necessary to destroy
objects and if I run the macro again to recreate the button, I get a
Run_time error saying that the item with the specified name wasn't found. I
understand why it is happening, but don't know what the workaround can be,
bearing in mind that I will need to frequently destroy the button and
recreate it. Can one force the button to be created with a specific name?
Can anyone help? My code is below;

Sub BuildButton()
ActiveSheet.Buttons.Add(4.5, 3, 72, 72).Select
Selection.OnAction = "Connect2Servers"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Connect to Servers"
With Selection.Characters(Start:=1, Length:=18).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.ScaleWidth 2.19, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementTop -0.75
Selection.ShapeRange.IncrementTop -0.75
Selection.ShapeRange.IncrementTop -0.75
Selection.ShapeRange.IncrementTop -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementLeft -0.75
Selection.ShapeRange.IncrementTop -0.75
End Sub

Many Thanks - Grant

 

[Chip Pearson]

Grant,

If you have only one button on the sheet, use code like

ActiveSheet.Buttons(1).Name = "TheButton"

You can then delete that shape with code like

ActiveSheet.Buttons("TheButton").Delete

Alternatively, in your recorded code, delete the line

ActiveSheet.Shapes("Button 1").Select

and replace it with

Selection.Name = "TheButton"




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dick Kusleika]

Grant

You shouldn't need to know the name. You can use a variable to hold the new
button and use that. Here's an example

Sub test()

Dim btn As Button
Dim i As Long

Set btn = ActiveSheet.Buttons.Add(4.5, 3, 72, 72)

With btn
.OnAction = "Connect2Servers"
.Characters.Text = "Connect to Servers"
With .Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With .ShapeRange
.ScaleWidth 2.19, msoFalse, msoScaleFromTopLeft
For i = 1 To 5
.IncrementTop -0.75
Next i
For i = 1 To 7
.IncrementLeft -0.75
Next i
End With
End With

End Sub

 

[Jamal]

Grant,

The problem is being caused the name "Button 1"

Insert a line after the first line and add this:
Selection.Name = "AB"

Change the fourth (3rd in the old macro) line to this:
ActiveSheet.Shapes("AB").Select

It does now work on my machine.

Regards

Jamal

 

[Grant Reid]

Hi

Many thanks to all who responded. Don't want to warble on too much, but this
group is a fantastic resource. Keep it up.

Cheers - Grant