Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning name to button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning name to button
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 "Grant Reid" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning name to button
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 -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Grant Reid" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning name to button
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 -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning name to button
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a macro to a button | Excel Worksheet Functions | |||
Assigning A Macro to A Button | Excel Worksheet Functions | |||
Help in assigning a command button | Excel Discussion (Misc queries) | |||
assigning macro to button | Excel Discussion (Misc queries) | |||
Assigning macro to button | Excel Discussion (Misc queries) |