Solved: Delete a Worksheet Command Button

[zoom38]

Hello all, I have a worksheet that I am trying to remove a command button that is in the range "b3:b4" named "Weekly & Daily Sort". None of the commands below are working. Can someone steer me in the right direction.


[VBA]'Worksheets("Sheet3").Shapes("Weekly & Daily Sort").Delete
Worksheets("Sheet3").Shapes("Weekly & Daily Sort").Delete
'Worksheets("Sheet3").Buttons("Weekly & Daily Sort").Visible = False
'Worksheets("Sheet3").Shapes("Weekly & Daily Sort").Visible = False
[/VBA]
Thanks
Gary

[jolivanes]

Is that the actual name of the button?
Or is it the caption?

[jolivanes]

If it is a button from the Forms toolbar,

ActiveSheet.Shapes("Button 1").Cut

If it is a button from the Control Toolbox,

ActiveSheet.Shapes("CommandButton1").Cut

Change the button names (numbers?) accordingly

HTH
John

[zoom38]

Good question, I believe it is the caption now that you mention it. But I tried using "Button1", "Button 1", "Command Button 1" but still no success. How do I find out the name of the button?

Thanks

[zoom38]

Neither of the following worked either.

ActiveSheet.Shapes("Button 1").Cut

ActiveSheet.Shapes("CommandButton1").Cut

I think I've found out the problem. Because I am doing it alot the button name is changing. I'm up to "Button 37". How do I find out the button name using code so I can delete it.

Thanks
Gary

[zoom38]

I continually get the message "The item with the specified name wasn't found" but the button is there. How do I address this?

Thanks
Gary

[mikerickson]

If you select the button, its name will appear in the names window next to the formula bar.

[zoom38]

Yes I do see that but now It is up to Button 41, how do I find its name using code so I can delete it. Let me explain a little further. I am copying my first 2 worksheets into sheet 3 for condensed printing. I do not want the button to be copied to sheet 3 or I would like to delete it from sheet 3. Because it is begin copied multiple times the name is changing. What can I do to stop this.

Thanks
Gary

[mikerickson]

So you are repeatedly DeleteButton-CopySheet-RemakeButton?

How many buttons do you have on the sheet? If this is the only one, perhaps something like
[VBA]ActiveSheet.Shapes(1).Delete[/VBA]or[VBA]With ActiveSheet
.Shapes(.Shapes.Count).Delete
End With[/VBA]

Rather than re-making the button every time you copy the sheet you could delete the copied button from the new sheet.

[jolivanes]

Or you could just name the button "Charlie" and put that in you code.

[jolivanes]

Like Mike mentioned, I do it as follows:

Sheets("Whatever").Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = "NewName"
            .Shapes("Charlie").Delete
         End With

HTH
John

[zoom38]

Sorry for getting back to you so late, but thank you very much.
Mike I used your code below which worked great
[VBA]With ActiveSheet
.Shapes(.Shapes.Count).Delete
End With [/VBA]

Thanks Again
Gary

[lucas]

Bob has pointed out before that data validation and filtering are also affected when you delete all shapes.......hope you don't have any data validation on the sheet.