Thread: Worksheet name
View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips[_4_] Bob Phillips[_4_] is offline
external usenet poster
 
Posts: 834
Default 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,