PDA

View Full Version : Solved: Delete a Worksheet Command Button



zoom38
05-20-2009, 06:44 PM
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.


'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

Thanks
Gary

jolivanes
05-20-2009, 06:55 PM
Is that the actual name of the button?
Or is it the caption?

jolivanes
05-20-2009, 07:03 PM
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
05-20-2009, 07:04 PM
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
05-20-2009, 07:11 PM
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
05-20-2009, 07:20 PM
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
05-20-2009, 07:31 PM
If you select the button, its name will appear in the names window next to the formula bar.

zoom38
05-20-2009, 07:38 PM
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
05-20-2009, 07:51 PM
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
ActiveSheet.Shapes(1).DeleteorWith ActiveSheet
.Shapes(.Shapes.Count).Delete
End With

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

jolivanes
05-20-2009, 08:18 PM
Or you could just name the button "Charlie" and put that in you code.

jolivanes
05-20-2009, 08:55 PM
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
05-25-2009, 09:51 AM
Sorry for getting back to you so late, but thank you very much.
Mike I used your code below which worked great
With ActiveSheet
.Shapes(.Shapes.Count).Delete
End With

Thanks Again
Gary

lucas
05-25-2009, 09:58 AM
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.