copy, move, rename sheet- without buttons?

[darro]

I have this code:

Sub move()
sn = InputBox("Enter Sheet Name")
    ActiveSheet.Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = sn
MsgBox "New Sheet Added"
End Sub

This works well but I have a few problems.

1. If I click Cancel on the Input Box I get an error and it still adds a sheet anyway, just with no new name.
2. It has the macro button on the new sheet, ideally I would like this not to be copied over.


Can anyone figure out what I need to do to get this to work?

Thanks for any help, much appreciated

 

[Dan Waterloo]

You can check the value before you do anything. If it's blank, skip.

 

[Lake]

1. Normally when I use application boxes I have statement that accounts for no.

I think if you had an if statement where sn = false then exit sub you would be ok

If sn = False Then
Exit Sub
Else...

2. ActiveSheet.Shapes("Button 1").Select
Selection.Delete

Should delete the button for you.

 

[darro]

thanks for the replies, Lake, where would I put those in the code?

 

[Lake]

This should work for you, assuming you have only one button and its a regular form control button.

Sub move()
 
sn = Application.InputBox("Enter Sheet Name")
If sn = False Then
Exit Sub
Else
    ActiveSheet.Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = sn
MsgBox "New Sheet Added"
End If
If ActiveSheet.Shapes.Count > 0 Then
ActiveSheet.Shapes("Button 1").Select
Selection.Delete
Else
End If
 
End Sub

 

[darro]

thanks Lake, i will give that a try.

cheers for the help

 

[darro]

just a quick question,


how do I know its a regular form control button?

 

[Lake]

When you insert a control normally you have the option of picking an activex control or a form conrtol. They both look the same but are different when working with vba.

Odds are its the form control.

 

[darro]

Hi.

This works well. However, it does not delete the button if it's not Button 1. How can I specify the button number I want deleted?

This is so close to perfect.

Thanks for your help?

 

[Lake]

An easy way to check the name of the button is to right click on it, the name should appear just left of the formula bar. Try changing the ("Button 1") to match yours.
Hope that helps.