tabs
 

I am still having problems with tab naming
I have a sheet named directory and in column c from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

tabs
 

The code you posted allows the user to put a name in cell A1 of
the active sheet and then activate the code to assign that name
to that sheet.

If you want the names in column C to be used for assignment to
the work sheets, you need to specify:
1. Do the 20 sheets already exist or will new sheets be required?
2. Will every sheet in the workbook be named from the names in
Column C or are you using additional sheets for other data?
3. If you are using other sheets, how many and do you want them
to be the first sheet(s) on the left or last on the right.

The sheets will be named in the same order as the names are
listed in Column C from top to bottom.

"duckie" wrote:

I am still having problems with tab naming
I have a sheet named directory and in column c from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

tabs
 

If you only have twenty sheets in the workbook, here is the code.
If if gives an error, post back.

Sub nmsh()
Dim lstRw, x, i As Long
lstRw = Cell(Rows.Count, 3).End(xlUp).Row
x = 1
For i = 4 To 23
SHeets(x).Name = Cells(i, 3).Value
x = x + 1
Next
End Sub

"duckie" wrote:

I am still having problems with tab naming
I have a sheet named directory and in column c from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

tabs
 

The code I gave you goes in the standard module, NOT the sheet module.

"JLGWhiz" wrote:

If you only have twenty sheets in the workbook, here is the code.
If if gives an error, post back.

Sub nmsh()
Dim lstRw, x, i As Long
lstRw = Cell(Rows.Count, 3).End(xlUp).Row
x = 1
For i = 4 To 23
SHeets(x).Name = Cells(i, 3).Value
x = x + 1
Next
End Sub

"duckie" wrote:

I am still having problems with tab naming
I have a sheet named directory and in column c from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

tabs
 

On Jan 30, 2:12*pm, JLGWhiz wrote:
The code you posted allows the user to put a name in cell A1 of
the active sheet and then activate the code to assign that name
to that sheet.

If you want the names in column C to be used for assignment to
the work sheets, you need to specify:
1. *Do the 20 sheets already exist or will new sheets be required?
2. *Will every sheet in the workbook be named from the names in
* * Column C or are you using additional sheets for other data?
3. *If you are using other sheets, how many and do you want them
* * to be the first sheet(s) on the left or last on the right.

The sheets will be named in the same order as the names are
listed in Column C from top to bottom.



"duckie" wrote:
I am still having problems with tab naming
I have a sheet named directory and in column c *from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
*I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
* * * * Const sNAMECELL As String = "A1"
* * * * Const sERROR As String = "Invalid worksheet name in cell "
* * * * Dim sSheetName As String
* * * * With Target
* * * * * * If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
* * * * * * * * sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
* * * * * * * * If Not sSheetName = "" Then
* * * * * * * * * * On Error Resume Next
* * * * * * * * * * Me.Name = sSheetName
* * * * * * * * * * On Error GoTo 0
* * * * * * * * * * If Not sSheetName = Me.Name Then _
* * * * * * * * * * * * MsgBox sERROR & sNAMECELL
* * * * * * * * End If
* * * * * * End If
* * * * End With
* * End Sub- Hide quoted text -

- Show quoted text -

i already have the workbook and all the names are only on one sheet
called the directory

tabs
 

On Jan 30, 2:21*pm, JLGWhiz wrote:
If you only have twenty sheets in the workbook, here is the code.
If if gives an error, post back.

Sub nmsh()
* * * * * * * * Dim lstRw, x, i As Long
* * * * lstRw = Cell(Rows.Count, 3).End(xlUp).Row
* * * * x = 1
* * * * For i = 4 To 23
* * * * * * * * SHeets(x).Name = Cells(i, 3).Value
* * * * * * * * x = x + 1
* * * * Next
End Sub



"duckie" wrote:
I am still having problems with tab naming
I have a sheet named directory and in column c *from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
*I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
* * * * Const sNAMECELL As String = "A1"
* * * * Const sERROR As String = "Invalid worksheet name in cell "
* * * * Dim sSheetName As String
* * * * With Target
* * * * * * If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
* * * * * * * * sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
* * * * * * * * If Not sSheetName = "" Then
* * * * * * * * * * On Error Resume Next
* * * * * * * * * * Me.Name = sSheetName
* * * * * * * * * * On Error GoTo 0
* * * * * * * * * * If Not sSheetName = Me.Name Then _
* * * * * * * * * * * * MsgBox sERROR & sNAMECELL
* * * * * * * * End If
* * * * * * End If
* * * * End With
* * End Sub- Hide quoted text -

- Show quoted text -

i am very new to this type of work in excel

tabs
 

I modified this to avoid over writing your sheet named "Directory" other
instructions are included below.

Sub nmsh()
Dim lstRw, x, i As Long
lstRw = Cell(Rows.Count, 3).End(xlUp).Row
x = 2
For i = 4 To 23
If Sheet.Name < "Directory"
Sheets(x).Name = Cells(i, 3).Value
x = x + 1
End If
Next
End Sub

To use this code, while holding the Alt key, press the F11 key [Alt + F11].
This opens the Visual Basic Editor window. If the large window is dark,
click Insert on the menu bar of the VBE window, then click module. That
should display a lighter screen in the code window. Paste the code above
into the window. Then activate the Excel window and click ToolsMacroMacros
which will display a dialog box. If "nmsh" appears in the small window at
the top of the dialog box, then click Options. If it does not appear in the
small window, find it in the larger window and click it, it should then show
in the smaller window, then click Options. Another dialog box will appear
with a very small input window and a caption that says Ctl +.
Type a keyboard letter into that small box then click OK. It is not
necessary to enter anything in the Description box, unless you might think
you will forget what the macro is for. If you enter anything in the
description box, do it before clicking OK. You can then use the Ctl plus the
letter you chose to start the macro from the keyboard instead of having to
open the macro dialog box each time to run your macro.


"duckie" wrote:

On Jan 30, 2:21 pm, JLGWhiz wrote:
If you only have twenty sheets in the workbook, here is the code.
If if gives an error, post back.

Sub nmsh()
Dim lstRw, x, i As Long
lstRw = Cell(Rows.Count, 3).End(xlUp).Row
x = 1
For i = 4 To 23
SHeets(x).Name = Cells(i, 3).Value
x = x + 1
Next
End Sub



"duckie" wrote:
I am still having problems with tab naming
I have a sheet named directory and in column c from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub- Hide quoted text -

- Show quoted text -

i am very new to this type of work in excel

tabs
 

On Jan 31, 12:59*am, JLGWhiz
wrote:
I modified this to avoid over writing your sheet named "Directory" *other
instructions are included below.

Sub nmsh()
* * * * * * * * *Dim lstRw, x, i As Long
* * * * lstRw = Cell(Rows.Count, 3).End(xlUp).Row
* * * * x = 2
* * * * For i = 4 To 23
* * * * * * * * * * * *If Sheet.Name < "Directory"
* * * * * * * * Sheets(x).Name = Cells(i, 3).Value
* * * * * * * * x = x + 1
* * * * * * * * * * * *End If
* * * * Next
*End Sub * * * *

To use this code, while holding the Alt key, press the F11 key [Alt + F11].. *
This opens the Visual Basic Editor window. *If the large window is dark,
click Insert on the menu bar of the VBE window, then click module. *That
should display a lighter screen in the code window. *Paste the code above
into the window. *Then activate the Excel window and click ToolsMacroMacros
which will display a dialog box. *If "nmsh" appears in the small window at
the top of the dialog box, then click Options. *If it does not appear in the
small window, find it in the larger window and click it, it should then show
in the smaller window, then click Options. *Another dialog box will appear
with a very small input window and a caption that says Ctl +.
Type a keyboard letter into that small box then click OK. *It is not
necessary to enter anything in the Description box, unless you might think
you will forget what the macro is for. *If you enter anything in the
description box, do it before clicking OK. *You can then use the Ctl plus the
letter you chose to start the macro from the keyboard instead of having to
open the macro dialog box each time to run your macro.



"duckie" wrote:
On Jan 30, 2:21 pm, JLGWhiz wrote:
If you only have twenty sheets in the workbook, here is the code.
If if gives an error, post back.

Sub nmsh()
* * * * * * * * Dim lstRw, x, i As Long
* * * * lstRw = Cell(Rows.Count, 3).End(xlUp).Row
* * * * x = 1
* * * * For i = 4 To 23
* * * * * * * * SHeets(x).Name = Cells(i, 3).Value
* * * * * * * * x = x + 1
* * * * Next
End Sub

"duckie" wrote:
I am still having problems with tab naming
I have a sheet named directory and in column c *from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
*I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
* * * * Const sNAMECELL As String = "A1"
* * * * Const sERROR As String = "Invalid worksheet name in cell "
* * * * Dim sSheetName As String
* * * * With Target
* * * * * * If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
* * * * * * * * sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
* * * * * * * * If Not sSheetName = "" Then
* * * * * * * * * * On Error Resume Next
* * * * * * * * * * Me.Name = sSheetName
* * * * * * * * * * On Error GoTo 0
* * * * * * * * * * If Not sSheetName = Me.Name Then _
* * * * * * * * * * * * MsgBox sERROR & sNAMECELL
* * * * * * * * End If
* * * * * * End If
* * * * End With
* * End Sub- Hide quoted text -

- Show quoted text -

i am very new to this type of work in excel- Hide quoted text -

- Show quoted text -

i am still having problems i was wondering if i could email the
workbook to you