How do I Programmatically Delete a Button?

[bdmangum]

Here's scenario I'm running: I have a macro which will allow the user to insert as many Titles as they desire. Each Title they insert is insertd via InputBox. For each Title they insert the macro automatically places the Title in a sheet and inserts a button in the cell next to the Title.

Here's the problem: Programmatically adding the buttons is easy, programmatically deleting them however seems to be a problem. The user can use a form to select which Title they wish to delete. Everything deletes fine except I can't get the button to delete. I can't find a way to reference the button. Since the button is created automatically, and the user selects the Title for deleting I can't use an actual shape name for the button, thus I need to delete it by the cell it resides in.

Any ideas how I should do this?

 

[stevexff]

Can you post the code that adds the buttons - might give us a clue as to an easy way to keep track of them for later deletion.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[bdmangum]

WVar = 1
RowVar = 2
ColVar = 0

WSUVar = 1
While Sheets(3).Cells(3, WSUVar).Value <> ""

'Sets formula
Sheets(4).Cells(WVar, 1).FormulaR1C1 = "=Sheet3'!R[" & RowVar & "]C[" & ColVar & "]"

'Sets format
With Sheets(4).Cells(WVar, 1)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Name = "Arial"
.Font.Size = 18
.Font.Strikethrough = False
.Font.Superscript = False
.Font.Subscript = False
.Font.OutlineFont = False
.Font.Shadow = False
.Font.Underline = xlUnderlineStyleSingle
.Font.ColorIndex = xlAutomatic
.Font.Bold = True
.Font.Italic = True
.Font.Underline = xlUnderlineStyleSingle
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
End With


'Inputs button
If Cells(WVar, 2).Value <> "Valid" Then
Cells(WVar, 2).Font.ColorIndex = 2
Cells(WVar, 2).Value = "Valid"

'Sets Cell position and size variables
Sheets(4).Activate
Cells(WVar, 2).Activate
CellX = ActiveCell.Left
CellY = ActiveCell.Top
CellH = 23.25
CellW = 86.25

With ActiveSheet.Buttons.Add(CellX, CellY, CellW, CellH)
.Characters.Text = "Macro Button"
.OnAction = "Some_Macro"
End With

End If

'Sets new values
WSUVar = WSUVar + 1
RowVar = RowVar - 50
ColVar = ColVar + 1
WVar = WVar + 50

Wend

 

[stevexff]

I can't find Buttons.Add() in my Excel 2002 help. From a bit of googling, I found

http://www.mrexcel.com/archive/Controls/19553.html,

a posting from five years ago indicating that even back then it was an undocumented method regarded as 'archaic' by MS and supported for backwards compatibility only.

You might want to revisit the way you add the buttons to bring it up to date. After that, it may be easier to handle the deletion?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[bdmangum]

Thanks again for the response Steve. Would you happen to know another or better way to add the buttons? I guess I could use a create object code line, but I'm not sure if that will be any better.

I've considered that we could assign some hidden, unique name to each button as it is added. We could possibly do this by using a name based upon the Title in the nearby cell. But I've tried several ways of this and could not get it to work.

The other idea I've had is to see if we could delete all shapes or objects within a range. This would work easily due to when it's deleted it simply deletes the rows. But I'm not sure how to delete all objects in a range, it may not be possible.

Ideas?

 

[HughLerwill]

Stuff like;

.ActiveWorkBook.Sheets(1).Buttons(1).Delete

is working for me.

regards Hugh,

 

[HughLerwill]

'is working for me' when automating from vb6

and from VBA too, I just tested this;
To delete all Buttons on Sheet1
Put this in some Sheet1 code;

Dim b as Button
For Each b in Buttons
b.delete
next

Pressing F1 when 'Buttons' is highlighted in code returns 'Hidden element ....' for me in Excel XP, but if I open Object browser search for Buttons - right click and opt to show hidden Members - then 'Button' is exposed along with its Functions and Properties...Hugh

 

[bdmangum]

The problem is that neither of those two solutions will work for me. There isn't a specific button I'll need to delete, nor do I need to delete all of them. Thus my problem. The user will select from a user form which topic they wish to delete from the sheet. Then the macro has to find the rows containing the data and delete them (which is easy). The problem is that there is a button in one of the rows which also must be deleted. There has to be a way to auto-select and delete a certain button based upon a user prompt.

 

[CBasicAsslember]

For each Title they insert the macro automatically places the Title in a sheet and inserts a button in the cell next to the Title.

Next to the title means? Next column to the right? Left? Are all the buttons in the same row?

The user can use a form to select which Title they wish to delete.

I would like to see the code that deletes the Title.

 

[CBasicAsslember]

This should work. Make a button.

Sub CreateButton()

Dim CellX As Long
Dim CellY As Long
Dim CellH As Long
Dim CellW As Long
Dim oNewButton As Object
Dim sTitle As String

' Temp variable to store Title name
sTitle = "Some Title"
            
' Active cell is where the button is going to be placed
CellX = ActiveCell.Left
CellY = ActiveCell.Top

' Buttons height and width
CellH = 12.75
CellW = 50
                        
            ' Add a Forms Control 'Button'
            With ActiveSheet
                Set oNewButton = .Buttons.Add(CellX, CellY, CellW, CellH)
                    
                    With oNewButton
                        ' Name the button
                        .Name = sTitle
                        .Characters.Text = "Macro Button"
                        .OnAction = "Some_Macro"
                    End With
            End With
End Sub

Delete a button.

Sub DeleteButton()
Dim oButton As Object
Dim sTitle As String

' Name of button you want to delete
sTitle = "Some Title"

Set oButton = ActiveSheet.Buttons

    ' Loop through all the buttons
For Each oButton In ActiveSheet.Buttons
    ' Delete the button with the name assigned to sTitle
    If oButton.Name = sTitle Then oButton.Delete
Next
End Sub

 

[stevexff]

Everyone seems to be quite happy writing new code at the end of 2006, with methods that were declared obsolete when Excel 97 came out.

I appreciate that we all have deadlines to meet, but am I the only person here who thinks this is a really bad idea?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[combo]

A button (excel 5-7) is a shape too, so you can loop through all shapes and test 'TopLeftCell' or 'BottomRightCell' property.
Steve,
form controls are obsolete, but do you know that MS still uses (excel xp) old dialog boxes and excel 4 macro sheets in solver? Anyway, they are quite useful as light-weight controls on the sheet, can be used witout coding. And by 'Application.caller' you can assign one macro to multiple objects, that sometimes can be useful.

combo

 

[CBasicAsslember]

Steve,

A couple of things. Look at the link you provide where the forms controls were declared "archaic". The author states "Button object is not documented in Excel 97 and beyond". I have Excel 2000 and the button objects are in fact documented in help. You should also notice that the same author that declared the form controls "archaic" also suggests using them in the second post on the same web page. Back when that web page was written Microsoft was extolling the virtues of ActiveX controls.

It is now almost 2007 and the forms controls are alive and kicking. In Excel 2007:

In previous versions of Excel you have to enable the forms toolbar to add the controls, in Excel 2007 they are located on the developer tab.

There is no mention of the controls being there for backward compatibility. A post written in 204 by a Microsoft MVP does not seem to share your concern.

 

[stevexff]

OK, OK, I submit! Seems fairly safe in this case.

I've seen plenty of instances over the years of people ignoring warnings about obsolescence, and coding what they know already to get the job done, only to discover that they can't migrate to version n because they've got to rewrite all their custom code...

I didn't want us to be giving duff advice

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[combo]

Steve,
you are right that MS hide old controls' interface. Instead, provided a new way to access them:

Dim oShapeFC As Shape
Set oShapeFC = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 5, 5, 60, 20)
oShapeFC.OnAction = "MyMacro"

combo

 

[bdmangum]

Thanks for the responses guys.

Combo,

I think your code should work fine for my scenario. I'd post the delete code I'm using now, but it's at work and I won't be back in to work until after New Years.

I'll let ya'll know if there are anyproblems when I insert that code, but I don't expect any to arise.

Thanks!

 

[Kerik]

Hello everyone, first time here

I had a small? problem, about adding and deleting buttons. When you add button using buttons.add it gains name like "Button (number)" (let say - "Button 44444"). When you add next button, number is increased by 1 (let say - "Button 44445"). Even when you delete button, the next one has always increased number (when you delete "Button 44444" the next one creted has number 44446).

I've created simple code to quickly add/delete buttons:

Sub test_buttons.add()
For i = 1 To 65540
ActiveSheet.Buttons.Add(40, 40, 100, 100)
Selection.Name = "test" & i
ActiveSheet.Shapes("test" & i).Select
ActiveSheet.Shapes("test" & i).Delete
Next i

It seems that Excel is confused, when you create over 65536. The next button cannot be created in the same macro-run. (Run-time error 1004). I realized that normally that
when all buttons are deleted that number (counter?) is reseted when you close/open document. But when at least one button left counter is not reseted.

Thanks to CBasicAsslember (Programmer) I've modified code:
Dim oNewButton as Object
For i = 1 To 65540
With ActiveSheet
Set oNewButton = .Buttons.Add(40, 40, 100, 100)
With oNewButton
.Name = "test" & i
End With
End With
ActiveSheet.Shapes("test" & i).Select
ActiveSheet.Shapes("test" & i).Delete
Next i

and it works perfectly (event without Dim). I just wonder - because I'm begginner - why it is so big difference between
ActiveSheet.Buttons.Add(40, 40, 100, 100)
and
With ActiveSheet
Set oNewButton = .Buttons.Add(40, 40, 100, 100)
End With

Could anyone explain that to me in easy way?
Or just where to look for explaination?

*regards*

Kerik

 

[PHV]

In fact you play with an clearly instantiated object (oNewButton) instead of the vague Selection stuff.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CBasicAsslember]

In the VBA editor go to 'Tools' --> 'Options', a popup box will appear. Under the 'Editor' tab check the box for 'Require Variable Declaration'. This will automatically put 'Option Explicit' at the top of all code modules. This will force you to declare all your variables which can save you a lot of headaches. For example, if you use a variable more then once in a routine and misspell it once it will be treated as a different variable. When your required to declare the variables the misspelled variable will generate an error telling you that it wasn't declared. By default an undeclared variable will be declared as Variant which uses more memory and will slow down your code.

Remove the line of code:

ActiveSheet.Shapes("test" & i).Select

You don't need to select the button to delete it.

With:

 ActiveSheet.Buttons.Add(40, 40, 100, 100)

I get a syntax error requiring an "=".