naming a worksheet

P

praptisahni

i want to control how the sheets are named, for instance how do i ensur
that when i click on Insert Worksheet i will get a worksheet with le
us say the name 'Prapti' instead of Sheet1,Sheet2 etc.
Also can i change the last nos of the sheet, for instance if i creat
Sheet3 and then delete it , the next sheet is created with the no
Sheet4 and not Sheet3, how can i change tha
 
J

Jason Morin

Try something like:

Sub NameNewSheet()
Dim sName As String
Dim wTemp As Worksheet

Sheets.Add.Name = "wTemp"
sName = InputBox("Name of new sheet:")
Sheets("wTemp").Name = sName

End Sub

Of course it'll error out if a ws with the same name
already exists.

HTH
Jason
Atlanta, GA
 
R

Rollin_Again

Why are you wanting to do this? Is this just a matter of persona
preference or do you have a real need for this to be done?

The only way I know how to accomplish what you want is to add some VB
code to the Workbook_NewSheet event. You would have to add some logi
to loop through all the existing sheets in the workbook to determine i
the name you are giving each new sheet already exists. I think it
really more trouble than its worth.


Rolli
 
B

Bob Phillips

Add this code to the Thisworkbook

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = "Prapti" & Mid(Sh.Name, 6, 9)
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Rollin_Again

Bob said:
*Add this code to the Thisworkbook

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = "Prapti" & Mid(Sh.Name, 6, 9)
End Sub

Bob Phillips
*
Click to expand...


Bob, your code works but it will not assign the next available shee
number if you have deleted a sheet from the number sequence. This i
one of the things Praptisahni mentioned in his original post.
*Also can i change the last nos of the sheet, for instance if
create Sheet3 and then delete it , the next sheet is created with th
nos Sheet4 and not Sheet3, how can i change that *
Click to expand...


Even though my code is ugly, it works this way.

Rolli
 
B

Bob Phillips

You are right, didn't catch that. The code would be relatively easy but I
wonder what the OP wants if you have Sheet1,2,3,4,5 and Sheet3 gets deleted.
Does he want the next created be Sheet3 or Sheet6.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top