Inserted (new) sheet names to be simple sequential numerics 2, 3, 4

[Max]

Hi guys,

I tried to customise the default book opened to be one with a
single sheet, named "1" (without the quotes), and I wanted
inserted (new) sheet names hence to be simple sequential numerics
2, 3, 4 etc.

I saved as Book.xlt and Sheet.xlt in xlstart folder.

Testing revealed that the default book opened is ok, with a
single sheet named "1", but repeated clicking of Insert>Worksheet
gave new sheets with names of "1 (2)", "1 (3)", etc instead of
the desired "2", "3", etc.

How could the desired new sheet naming viz. simple sequentials 2,
3, 4, etc be achieved?

TIA
Max

 

[Max]

Thanks, JP. Followed your instructions, but I could not get this to work.
Still the same results, viz Insert>Worksheet gave new sheets with names
of "1 (2)", "1 (3)", etc instead of the desired "2", "3", etc.

Any other suggestions to try?

 

[Max]

Thanks, Myrna. Tried your amendment but still no-go I'm afraid.
Same story.

 

[Dave Peterson]

Any chance you didn't put it under the ThisWorkbook module?

 

[Max]

No. I noted the instructions and was careful in the execution.

 

[Dave Peterson]

It worked ok for me.

Any chance you don't have macros enabled?

 

[Max]

No. But I'm using xl97. Could the version be an issue?
Perhaps you could email to me your template files
and I'll try these out. Thanks.

 

[Dave Peterson]

I use xl2002, but it's on its way.

(I wouldn't think that this would be a version difference, but I don't have xl97
to test it anymore.)

 

[Dave Peterson]

Maybe there is a version difference.

But try moving your template files out of XLStart and see if that does anything
different.

I do remember that xl97 did treat the new names differently when you have a
sheet.xlt template.

Maybe you could delay the change slightly:

Behind the workbook:
Option Explicit
Private Sub Workbook_NewSheet(ByVal sh As Object)
Application.OnTime Now, "renameIt"
End Sub

In a general module:
Option Explicit
Sub renameIt()
ActiveSheet.Name = Worksheets.Count
End Sub


It worked ok for me, but so did the other one.

 

[Dave Peterson]

I kind of remember being pleasantly surprised when xl2k didn't make the name of
the new sheet (#) when I had a sheet.xlt file.

But that doesn't explain why the code didn't work. But as long as you're happy!

 

[Max]

Dave, just thought i'll feedback further
(heck, its the least i can do in return <bg>).

i've tried out your 2nd option, the one with "Sub renameIt()" in
a general module.

It works too, but again, only in the absence of Sheet.xlt in
XLSTART.

If Sheet.xlt is in XLSTART, I'll hit the same problem.

cheers