Linking Tab Names to a single cell with a worksheet
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"? |
Linking Tab Names to a single cell with a worksheet
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"? |
Linking Tab Names to a single cell with a worksheet
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"? |
Linking Tab Names to a single cell with a worksheet
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"? |
Linking Tab Names to a single cell with a worksheet
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"? |
All times are GMT +1. The time now is 08:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com