1. ## Calculating (XP)

In a reasonable size workbook (about 50 sheets) I have 40 worksheets with the following macro (right click on tab and view code)

Private Sub Worksheet_Calculate()
If Me.Range("c1").Value <> "" Then
Me.Name = Me.Range("c1").Value
End If
End Sub

I've used it before but now I open the workbook and it calculates and calculates til in the end I have to go to manual calculations.

Any suggestions ??? Otherwise I will remove them before I hand over the workbook.

Thanks

2. ## Re: Calculating (XP)

I'd use the Worksheet_Change event instead of the Worksheet_Calculate event, and check whether the Target argument intersects C1 (if the name is entered by the user in C1) or the cells that contribute to the value of C1 (if C1 contains a formula). You could also use the Workbook_SheetChange event in the ThisWorkbook module.

3. ## Re: Calculating (XP)

C1 is a formula
The actual name is in B1 and the formula in C1 is =LEFT(B1,10) so that the tabs have the same size.
Names are used and some of them are rather large, and to avoid huge tabs names I do it like this.

Thanks

4. ## Re: Calculating (XP)

I'd use the Worksheet_Change event and check whether B1 has changed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.Range("B1"), Target) Is Nothing Then
If Not Me.Range("B1") = "" Then
On Error Resume Next
Me.Name = Left(Me.Range("B1"), 10)
End If
End If
End Sub

You wouldn't need the formula in C1.

5. ## Re: Calculating (XP)

Hi there
Sorry for the delay but was out of the office most of the day.

Is that formula correct ?? It asks me to debug.. On the second line there is a ) but not an opening one

Thanks heaps

6. ## Re: Calculating (XP)

The code is correct, but perhaps it's not displayed correctly on your PC. I have attached a screenshot.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•