VBA For Tab Name

[jdluke6545]

I recently posted about a pretty difficult VBA code and you folks here were absolutely great in resolving my problem.
Now, I have another one, in the same workbook but on different sheets, so I know I'm in the right place.
I have a VBA code that that makes a sheet tab name reference a cell value.
That code is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CELL1 As String
Dim CELL2 As String
Dim CELL3 As String
Dim TABNAME As String

CELL1 = Range("O1")
CELL2 = Range("O2")
CELL3 = Range("O3")
TABNAME = CELL1 & " " & CELL2

If Not Intersect(Target, Range("O1")) Is Nothing Then
'ActiveSheet.Name = ActiveSheet.Range("O1")
ActiveSheet.Name = TABNAME & " First"

End If
End Sub

and this is working flawlessly.
Now I made a couple minor changes to that same code to fit for a different sheet
which is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CELL1 As String
Dim CELL2 As String
Dim CELL3 As String
Dim TABNAME As String

CELL1 = Range("O1")
CELL2 = Range("O2")
CELL3 = Range("O3")
TABNAME = CELL1 & " " & CELL3

If Not Intersect(Target, Range("O1")) Is Nothing Then
'ActiveSheet.Name = ActiveSheet.Range("O1")
ActiveSheet.Name = "Summary - " & TABNAME

End If
End Sub

But this one is giving me the error:
"Run-time error: 1004:
The specified dimension is not valid for the current chart type"

And yes, I do have a couple of charts on this "Summary" sheet where as on the other sheets where I used the first code there are no charts on those, so no error.
So, what is it about having a chart on this "Summary" sheet that I can not use the code to do what I need?
And also, if I were to put a chart (or charts) on the other sheets would I get the same error? (I'm guessing I would)
But more importantly, what do I need to change in my code to make this work in a sheet with charts?

Thanks for the help !!!

 

[jdluke6545]

Oh... I did see this thread but didn't see a solution to my particular situation.

Error 1004: The specified dimension not valid for current chart type

I am relatively new to vba programming. I've figured out a number of things but this has me stumped. This is what I am trying to do from a workbook where I have the vba code: 1) open a second workbook located elsewhere 2) save into another location 3) run some code on this second workbook...

www.mrexcel.com

 

[jdluke6545]

From what I have been able to determine, the code:

ActiveSheet.Name = "name of sheet here"

will not work with sheets that have embedded charts.
so what what would be the proper code to use instead of "ActiveSheet" that I should use for a sheet with embedded charts?

 

[Gokhan Aycan]

Which line are you getting the error? I can rename a sheet with a chart on it.

Maybe try giving it a codename through VBA Editor and use it instead of activesheet.

Another option would be using "Me" keyword. Will work in sheet's own module only.

Me.Name = "whatever"

 

[jdluke6545]

This is my current code"

Private Sub Worksheet_Change(ByVal Target As Range)
    ' THIS AUTO UPDATES THE SHEET TAB NAME TO THE VALUE OF CELLS
    Dim CELL1 As String
    Dim CELL2 As String
    Dim CELL3 As String
    Dim TABNAME As String
    
    CELL1 = Range("O1")
    CELL2 = Range("O2")
    CELL3 = Range("O3")
    TABNAME = CELL1 & " " & CELL2
    
    If Not Intersect(Target, Range("O1:O3")) Is Nothing Then
        'ActiveSheet.Name = ActiveSheet.Range("O1:O3")
        ActiveSheet.Name = TABNAME & " T&M"
    End If
End Sub

The line that is debugging at is:

ActiveSheet.Name = TABNAME & " T&M"

I am using this code on a total of 4 sheets, Sheet1, Sheet2, sheet3, and sheet5. Sheet5 is giving me the the error.
But what's crazy is all sheets have a chart in them...???? Sheets1-3 have one chart and sheet5 has 3 charts.
why does the code work fine on sheets1-3 with a chart but not on sheet5 with 3 charts?

 

[Gokhan Aycan]

What's the value of TABNAME for Sheet5? Maybe you are not complying with sheet naming conventions.

Check out this: * Rules for Naming Microsoft Excel Worksheets *

 

[jdluke6545]

TABNAME is what what's in the code in post #5. TABNAME = CELL1 & " " & CELL2
But that's not it, I can put text (in quotes) and it still doesn't work. I've tried it. When the value is out of specs I get a different error message.
The code in all sheets is exactly the same now. I wanted the code to be the exact same while trying to figure this out error.

 

[Gokhan Aycan]

Is it possible to share a reproducable workbook?

 

[jdluke6545]

I'll see what I can do to get the sheets posted.