Setting default Tab Name

  • Thread starter Thread starter Cameron
  • Start date Start date
C

Cameron

I have this formula that takes the value of a cell (whose
value is based on a formula) and joins it together with
some text and creates the tab name as a result. I'm trying
to do the formula so that if the cell is blank or is = 0
then it will set the tab to a default value. But it
doesn't work. I keep getting "Run time error '7': out of
memory" Does anyone know a way to fix it? Basically there
are 3 sheets in the work book. The tab names are all based
on what is entered in one cell in the first worksheet,
with text added on so there is no duplication of names.


Private Sub Worksheet_Calculate()
On Error Resume Next
With Range("C3")
Me.Name = .Value & " Program"
On Error GoTo 0
If Me.Name <> .Value Then _
Me.Name = "XXX Program"

End With
End Sub
 
I have this formula that takes the value of a cell (whose
value is based on a formula) and joins it together with
some text and creates the tab name as a result. I'm trying
to do the formula so that if the cell is blank or is = 0
then it will set the tab to a default value. But it
doesn't work. I keep getting "Run time error '7': out of
memory" Does anyone know a way to fix it? Basically there
are 3 sheets in the work book. The tab names are all based
on what is entered in one cell in the first worksheet,
with text added on so there is no duplication of names.


Private Sub Worksheet_Calculate()
On Error Resume Next
With Range("C3")
Me.Name = .Value & " Program"
On Error GoTo 0
If Me.Name <> .Value Then _
Me.Name = "XXX Program"

End With
End Sub
Click to expand...

Why not use something like this:

With Range("C3")
If .Value <> "" Or .Value <> 0 Then
ActiveSheet.Name = .Value & " Program"
Else
ActiveSheet.Name = "XXX Program"
End With

SL
 
Hi Sander et all,

I had to tweak it a little and it works, but.... only on
the first time a value is entered. If I change the entry
in the cell that sets the tab names, it is only reflected
in the first tab, not the subsequent tabs. i.e. If I
enter "AAA" in to cell C4 on sheet 1, then the sheet 1
becomes named AAA. When this is done I want sheet 2 to be
named "AAA Program" and sheet 3 "AAA Vendor". It will do
this when I first open the workbook and enter something in
C4 on sheet 1. But if I change AAA to say BBB, sheet 1
becomes BBB but sheet to remain having AAA in it. How can
I get it to update?

Cameron
 
You are probably getting recursive calls to the calculate event. Try this
(which worked for me).

Private Sub Worksheet_Calculate()
Dim sh As Worksheet
Dim sName As String
varr = Array("", " Program", " Vendor")
sName = CStr(Worksheets(1).Range("C4").Value)
If sName = "" Or sName = "0" Then
sName = "XXX"
End If
On Error GoTo ErrHandler
Application.EnableEvents = False
For i = 0 To 2
Worksheets(i + 1).Name = sName & varr(LBound(varr) + i)
Next
ErrHandler:
Application.EnableEvents = True
End Sub
 
Back
Top