+ Reply to Thread
Results 1 to 4 of 4

naming tab macro error runtime error 1004

  1. #1
    D
    Guest

    naming tab macro error runtime error 1004

    Gary DK very kindly provided a solution for naming tabs from the contents of
    a cell on a worksheet. A further problem has arisen:

    The contents of cell B7 on all 125 sheets provides the name for the sheet
    tab. Cell B7 gets its information from a separate master sheet.

    When I sort the master sheet (because new entries have been added at the
    bottom and need to be arranged according to a ref. code) I get an error
    message: Runtime error 1004 Cannot rename a sheet to the same name as another
    sheet, a referenced object library or a workbook referenced by Visual Basic.

    this is the code:

    Option Explicit
    Private shname As String

    Private Sub Worksheet_Calculate()
    If Range("B7").Value <> shname Then
    shname = [b7].Value
    End If
    Me.Name = shname
    End Sub


    Any ideas?



  2. #2
    Otto Moehrbach
    Guest

    Re: naming tab macro error runtime error 1004

    Does B7 of each sheet have a formula that links back to the Master sheet?
    If so, your problem is that each of these formulas links back to a specific
    cell in the Master sheet. When you sort the Master sheet list, each formula
    will calculate and try to name the Me sheet the new name that is now in the
    linked cell.
    Not knowing everything you are doing, it is difficult to give advice.
    You might try adding code to your macro to delete the formula once the sheet
    is named. Also add code to not rename the sheet if B7 is blank. HTH Otto
    "D" <D@discussions.microsoft.com> wrote in message
    news:46873CC2-AAE1-41F9-A791-4A7DE4D19BBD@microsoft.com...
    > Gary DK very kindly provided a solution for naming tabs from the contents
    > of
    > a cell on a worksheet. A further problem has arisen:
    >
    > The contents of cell B7 on all 125 sheets provides the name for the sheet
    > tab. Cell B7 gets its information from a separate master sheet.
    >
    > When I sort the master sheet (because new entries have been added at the
    > bottom and need to be arranged according to a ref. code) I get an error
    > message: Runtime error 1004 Cannot rename a sheet to the same name as
    > another
    > sheet, a referenced object library or a workbook referenced by Visual
    > Basic.
    >
    > this is the code:
    >
    > Option Explicit
    > Private shname As String
    >
    > Private Sub Worksheet_Calculate()
    > If Range("B7").Value <> shname Then
    > shname = [b7].Value
    > End If
    > Me.Name = shname
    > End Sub
    >
    >
    > Any ideas?
    >
    >




  3. #3
    D
    Guest

    Re: naming tab macro error runtime error 1004

    Hi, Otto

    Thanks for that - yes, B7 has a formula e.g. =MASTER!C6. I do want the
    sheet tab to rename when I sort as the sheet content will change to reflect
    the new order in the master sheet.

    Is it possible to email you the file? If not, then let me know and I will
    try and describe more fully.

    Many, many thanks for your time and expertise!

    D

    "Otto Moehrbach" wrote:

    > Does B7 of each sheet have a formula that links back to the Master sheet?
    > If so, your problem is that each of these formulas links back to a specific
    > cell in the Master sheet. When you sort the Master sheet list, each formula
    > will calculate and try to name the Me sheet the new name that is now in the
    > linked cell.
    > Not knowing everything you are doing, it is difficult to give advice.
    > You might try adding code to your macro to delete the formula once the sheet
    > is named. Also add code to not rename the sheet if B7 is blank. HTH Otto
    > "D" <D@discussions.microsoft.com> wrote in message
    > news:46873CC2-AAE1-41F9-A791-4A7DE4D19BBD@microsoft.com...
    > > Gary DK very kindly provided a solution for naming tabs from the contents
    > > of
    > > a cell on a worksheet. A further problem has arisen:
    > >
    > > The contents of cell B7 on all 125 sheets provides the name for the sheet
    > > tab. Cell B7 gets its information from a separate master sheet.
    > >
    > > When I sort the master sheet (because new entries have been added at the
    > > bottom and need to be arranged according to a ref. code) I get an error
    > > message: Runtime error 1004 Cannot rename a sheet to the same name as
    > > another
    > > sheet, a referenced object library or a workbook referenced by Visual
    > > Basic.
    > >
    > > this is the code:
    > >
    > > Option Explicit
    > > Private shname As String
    > >
    > > Private Sub Worksheet_Calculate()
    > > If Range("B7").Value <> shname Then
    > > shname = [b7].Value
    > > End If
    > > Me.Name = shname
    > > End Sub
    > >
    > >
    > > Any ideas?
    > >
    > >

    >
    >
    >


  4. #4
    Otto Moehrbach
    Guest

    Re: naming tab macro error runtime error 1004

    D
    Yes, email the file and a more detailed explanation of what you want.
    If your file has sheets other than the Master and the sheets with the name
    changes, point those out to me. My email address is ottokmnop@comcast.net.
    Remove the "nop" from this address. Otto
    "D" <D@discussions.microsoft.com> wrote in message
    news:AF7765DB-1128-454D-B37A-DEC43023FB24@microsoft.com...
    > Hi, Otto
    >
    > Thanks for that - yes, B7 has a formula e.g. =MASTER!C6. I do want the
    > sheet tab to rename when I sort as the sheet content will change to
    > reflect
    > the new order in the master sheet.
    >
    > Is it possible to email you the file? If not, then let me know and I will
    > try and describe more fully.
    >
    > Many, many thanks for your time and expertise!
    >
    > D
    >
    > "Otto Moehrbach" wrote:
    >
    >> Does B7 of each sheet have a formula that links back to the Master sheet?
    >> If so, your problem is that each of these formulas links back to a
    >> specific
    >> cell in the Master sheet. When you sort the Master sheet list, each
    >> formula
    >> will calculate and try to name the Me sheet the new name that is now in
    >> the
    >> linked cell.
    >> Not knowing everything you are doing, it is difficult to give advice.
    >> You might try adding code to your macro to delete the formula once the
    >> sheet
    >> is named. Also add code to not rename the sheet if B7 is blank. HTH
    >> Otto
    >> "D" <D@discussions.microsoft.com> wrote in message
    >> news:46873CC2-AAE1-41F9-A791-4A7DE4D19BBD@microsoft.com...
    >> > Gary DK very kindly provided a solution for naming tabs from the
    >> > contents
    >> > of
    >> > a cell on a worksheet. A further problem has arisen:
    >> >
    >> > The contents of cell B7 on all 125 sheets provides the name for the
    >> > sheet
    >> > tab. Cell B7 gets its information from a separate master sheet.
    >> >
    >> > When I sort the master sheet (because new entries have been added at
    >> > the
    >> > bottom and need to be arranged according to a ref. code) I get an error
    >> > message: Runtime error 1004 Cannot rename a sheet to the same name as
    >> > another
    >> > sheet, a referenced object library or a workbook referenced by Visual
    >> > Basic.
    >> >
    >> > this is the code:
    >> >
    >> > Option Explicit
    >> > Private shname As String
    >> >
    >> > Private Sub Worksheet_Calculate()
    >> > If Range("B7").Value <> shname Then
    >> > shname = [b7].Value
    >> > End If
    >> > Me.Name = shname
    >> > End Sub
    >> >
    >> >
    >> > Any ideas?
    >> >
    >> >

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1