Macro help copy text box but keep the same textbox name.

[esitze]

Hi All,

Thanks for taking a look at this to see if you can help. I have the following macro in use and it works great. Only problem is after this macro creates my new sheet I have another formula in place that will not work because, when the new sheet is created the text box names change. Anyway to stop this??? The current macro is:

Private Sub CommandButton1_Click()
Worksheets("Towers Calc").Copy After:=Sheets(Sheets.Count)
shtname = InputBox("Enter Tower Equipment Number")
If shtname = ("") Then ActiveSheet.Name = "Tower"
If shtname = ("") Then Exit Sub
ActiveSheet.Name = shtname
End Sub

Thanks again!

 

[ranman256]

what text box? why do you need to copy the text box?
why not just use a cell? Every sheet has the same cell.

 

[esitze]

The two text boxes are attached to an image inside the shet which is copied by the macro.

 

[mikerickson]

What happens when you make a copy of a copy?

Do the names of things remain the same between the copy and it's copy?

 

[esitze]

Well now that is interesting, when I make a copy of a copy they do stay the same.

 

[mikerickson]

What is happening is that the copy process uses a default naming convention.
The original might have object names that come from different order of creation or user chosen names, but the copy process assigns names on its own plan. The first copy and its copy get their objects' names from the same default naming convention.

So, make a copy of your original. Fix the formulas. And then that copy will be your template page to be the source of all the other downstream copies.

 

[esitze]

Thank you very much.