PDA

View Full Version : Need help clearing (removing) buttons



TorpedoJuice
03-10-2009, 07:08 AM
I have the following code written within a sub to create buttons and assign those same buttons a macro on a given worksheet. The larger picture is that a user selects a day of the week and runs a macro. For each day of the week a variable list of buttons are returned to the screen. If the user selects the wrong day of the week...I want to be able to clear the buttons.

While stepping through the code...each button shows an incremental number (i.e. Button 408, Button 409)...

How do I assign names to the created buttons so that, if necessary, I can clear.

Thanks in advance for any help.




nTextLength = 0
nButtonLeft = 560.75
nButtonTop = 85.75
nButtonWidth = 87.75
nButtonHeight = 26.25
nLenButtonName = 0
cButtonName = ""
cMacroName = ""

For N = 1 To nButtons

Sheets(sFindDataWorksheet).Select
Range("A" & nYesStartRange).Select

Do While N <= nButtons

Sheets(sFindDataWorksheet).Select
Range("A" & nYesStartRange).Select
cButtonName = ActiveCell.Offset(0, 3).Value
nLenButtonName = Len(cButtonName)
cMacroName = ActiveCell.Offset(0, 4).Value


Sheets(sWorksheet).Select

ActiveSheet.Buttons.Add(nButtonLeft, nButtonTop, nButtonWidth, nButtonHeight).Select


With Selection.Characters(Start:=1, Length:=nLenButtonName).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3 'Red
End With

Selection.Characters.Text = Left(cButtonName, 32)
Selection.OnAction = cMacroName

N = N + 1
nYesStartRange = nYesStartRange + 1
nButtonTop = nButtonTop + 30
If nButtonTop >= 300 And nButtonLeft = 560.75 Then
nButtonTop = 85.75
nButtonLeft = 660.75
ElseIf nButtonTop >= 300 And nButtonLeft = 660.75 Then
nButtonTop = 85.75
nButtonLeft = 760.75
End If

Loop
Next

Kenneth Hobs
03-10-2009, 09:04 AM
Welcome to the forum!

When posting code, please paste your vba code between vba code tags. Press the vba button to insert the tags.

You code has some variables that are not declared. Always use
Option Explicit
as the first line of code in a module or top of a sheet object. You can set that in your vbe editor's options.

If you can put your code in an xls and attach it (paperclip icon), it would be easier to help. If you can't get your code to do what you want, on a sheet set it up manually so that we can see what you need.

mdmackillop
03-10-2009, 10:38 AM
This should delete buttons (assuming name is correct), leaving other shapes.

Option Explicit
Sub Macro1()
Dim sh as shape
For Each sh In ActiveSheet.Shapes
If Left(sh.Name, 6) = "Button" Then sh.Delete
Next
End Sub