Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating (XP)

    The code is correct, but perhaps it's not displayed correctly on your PC. I have attached a screenshot.
    Attached Images Attached Images
    • File Type: png x.png (5.3 KB, 0 views)

Posting Permissions

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