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,
|