I have tried modifying the VBA code using several of the previous
suggestions but it does not change.

I am trying to link a worksheets "TAB" name to a particular cell in a
pre-existing file.

Question?
Does the sheet need to be named as a default "Sheet1"?

P.S. I have tried the following with no success.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Manager wrote:
I have tried modifying the VBA code using several of the previous
suggestions but it does not change.

I am trying to link a worksheets "TAB" name to a particular cell in a
pre-existing file.

Question?
Does the sheet need to be named as a default "Sheet1"?

As long as you have already saved the Worksheet with the Tab name you want, I
think this is what your looking for.
Sub MyTab()
If Range("A1") = "" Then
[A1] =
"=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,225)"
End If
End Sub
--
Mike Q.


"Manager" wrote:

P.S. I have tried the following with no success.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Manager wrote:
I have tried modifying the VBA code using several of the previous
suggestions but it does not change.

I am trying to link a worksheets "TAB" name to a particular cell in a
pre-existing file.

Question?
Does the sheet need to be named as a default "Sheet1"?

Mike,

Don't mean to be dense but would "Filename" = the actual Excel file
name?
And I'm assuming that I can substitute cell "A!" for any other cell??

Mike Q. wrote:
As long as you have already saved the Worksheet with the Tab name you want, I
think this is what your looking for.
Sub MyTab()
If Range("A1") = "" Then
[A1] =
"=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,225)"
End If
End Sub
--
Mike Q.


"Manager" wrote:

P.S. I have tried the following with no success.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Manager wrote:
I have tried modifying the VBA code using several of the previous
suggestions but it does not change.

I am trying to link a worksheets "TAB" name to a particular cell in a
pre-existing file.

Question?
Does the sheet need to be named as a default "Sheet1"?

FIND(""]"",CELL(""FILENAME"",A1))+1,225)

The above part in the formula will find the " ] " and use it as the
"starting number plus 1" to give give you up to the next 225 characters.
cell("filename",A1) = C:\Quinn\Excel\[MySpreadsheet.xls]Sheet1
] = is the 34th character + 1 =35
so mid(start_num is 35 (S in Sheet1),Num_char is 225)
Of course my tabs only accepts 31 characters not 225. Guess I should fix
that.

--
Mike Q.


"Manager" wrote:

Mike,

Don't mean to be dense but would "Filename" = the actual Excel file
name?
And I'm assuming that I can substitute cell "A!" for any other cell??

Mike Q. wrote:
As long as you have already saved the Worksheet with the Tab name you want, I
think this is what your looking for.
Sub MyTab()
If Range("A1") = "" Then
[A1] =
"=MID(CELL(""FILENAME"",A1),FIND(""]"",CELL(""FILENAME"",A1))+1,225)"
End If
End Sub
--
Mike Q.


"Manager" wrote:

P.S. I have tried the following with no success.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Manager wrote:
I have tried modifying the VBA code using several of the previous
suggestions but it does not change.

I am trying to link a worksheets "TAB" name to a particular cell in a
pre-existing file.

Question?
Does the sheet need to be named as a default "Sheet1"?