Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I alphabetize the tabs on a spreadsheet with 25 tabs? | Excel Worksheet Functions | |||
How do I alphabetize the tabs on a spreadsheet with 25 tabs? | Excel Worksheet Functions | |||
sheet tabs is checked but I can't see my tabs | Excel Discussion (Misc queries) | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
hide tabs from view then lock tabs? | Excel Discussion (Misc queries) |