Worksheet name
Hi
What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Hi Walrus. I think that the only thing to do is to type them in separately.
I am not aware that you can pull one from the other automatically. Please hit Yes if my comments have helped. Thanks. "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Hi,
Right click your sheet tab, view code and paste the code in. Change A1 to the cell you want Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then On Error Resume Next Application.EnableEvents = False ActiveSheet.Name = Target.Value Application.EnableEvents = True End If End Sub Mike "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Just to add on to what Mike has posted.
If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Thanks guys...i found and used the following VBA Function and it worked
perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Walrus
That code will get you into trouble because you aren't trapping for illegal file names Include the error trap I posted at the top of your code On Error resume next Mike "walrus" wrote: Thanks guys...i found and used the following VBA Function and it worked perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Mike
Where should i include this "On Error resume next"?? "Mike H" wrote: Walrus That code will get you into trouble because you aren't trapping for illegal file names Include the error trap I posted at the top of your code On Error resume next Mike "walrus" wrote: Thanks guys...i found and used the following VBA Function and it worked perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Hi,
Like this Sub name_um() On Error Resume Next For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub Mike "walrus" wrote: Mike Where should i include this "On Error resume next"?? "Mike H" wrote: Walrus That code will get you into trouble because you aren't trapping for illegal file names Include the error trap I posted at the top of your code On Error resume next Mike "walrus" wrote: Thanks guys...i found and used the following VBA Function and it worked perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Walrus, the sheet event will change the sheet name as soon as the cell value
is changed. The macro which you posted needs to be run separately to change the tab names -- Jacob "walrus" wrote: Mike Where should i include this "On Error resume next"?? "Mike H" wrote: Walrus That code will get you into trouble because you aren't trapping for illegal file names Include the error trap I posted at the top of your code On Error resume next Mike "walrus" wrote: Thanks guys...i found and used the following VBA Function and it worked perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Mike/Jacob
Thanks. This works even better as i don't have to worry about older tabs being renamed. Regards, "Jacob Skaria" wrote: Walrus, the sheet event will change the sheet name as soon as the cell value is changed. The macro which you posted needs to be run separately to change the tab names -- Jacob "walrus" wrote: Mike Where should i include this "On Error resume next"?? "Mike H" wrote: Walrus That code will get you into trouble because you aren't trapping for illegal file names Include the error trap I posted at the top of your code On Error resume next Mike "walrus" wrote: Thanks guys...i found and used the following VBA Function and it worked perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
Worksheet name
Nor did yours trap it, it just ignored it. Better to handle it IMO
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Application.EnableEvents = False ActiveSheet.Name = ValidName(Target.Value) Application.EnableEvents = True End If End Sub Function ValidName(ByVal TheFileName As String) As String Dim RegEx As Object Set RegEx = CreateObject("vbscript.regexp") RegEx.Global = True RegEx.Pattern = "[\\/:\*\?""<\|]" ValidName = RegEx.Replace(TheFileName, "") Set RegEx = Nothing End Function --- HTH Bob Phillips "Mike H" wrote in message ... Walrus That code will get you into trouble because you aren't trapping for illegal file names Include the error trap I posted at the top of your code On Error resume next Mike "walrus" wrote: Thanks guys...i found and used the following VBA Function and it worked perfectly. Sub name_um() For Each ws In Worksheets ws.Name = ws.Range("D1").Value Next End Sub "Jacob Skaria" wrote: Just to add on to what Mike has posted. If you are looking at naming all tabs in a workbook use the workbook level Sheet Change event. Incase you are new to VBA set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Target.Text < "" Then On Error Resume Next Application.EnableEvents = False Sh.Name = Target.Text Application.EnableEvents = True End If End Sub -- Jacob "walrus" wrote: Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, |
All times are GMT +1. The time now is 11:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com