Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting default Tab Name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting default Tab Name
On Thu, 31 Jul 2003 06:29:11 -0700, Cameron wrote:
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting default Tab Name
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 -----Original Message----- On Thu, 31 Jul 2003 06:29:11 -0700, Cameron wrote: 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 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting default Tab Name
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 -- Regards, Tom Ogilvy Cameron wrote in message ... 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 -----Original Message----- On Thu, 31 Jul 2003 06:29:11 -0700, Cameron wrote: 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 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting default filter setting | Charts and Charting in Excel | |||
Default printer setting | Excel Discussion (Misc queries) | |||
Setting default formatting | Setting up and Configuration of Excel | |||
Setting default quantities | Excel Discussion (Misc queries) | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) |