+ Reply to Thread
Results 1 to 7 of 7

naming worksheets

  1. #1
    Registered User
    Join Date
    11-04-2005
    Posts
    10

    naming worksheets

    I want to match the name of a worksheet tab with the header of a form. In other words, if I type the name of the form in a header (a
    cell), the worksheet tab would change the name to match the header name.

    Here's what I'm using but it's not working:

    Private Sub workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
    If target.Address = "$d$1" Then sh.Name = target
    End Sub

    Can anyone tell me what I'm doing wrong? THANKS!

  2. #2
    Bob Phillips
    Guest

    Re: naming worksheets

    Has to be upper-case

    Private Sub workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    If Target.Address = "$D$1" Then sh.Name = Target.Value
    End Sub


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "jerrystan" <[email protected]> wrote
    in message news:[email protected]...
    >
    > I want to match the name of a worksheet tab with the header of a form.
    > In other words, if I type the name of the form in a header (a
    > cell), the worksheet tab would change the name to match the header
    > name.
    >
    > Here's what I'm using but it's not working:
    >
    > Private Sub workbook_SheetChange(ByVal sh As Object, ByVal target As
    > Range)
    > If target.Address = "$d$1" Then sh.Name = target
    > End Sub
    >
    > Can anyone tell me what I'm doing wrong? THANKS!
    >
    >
    > --
    > jerrystan
    > ------------------------------------------------------------------------
    > jerrystan's Profile:

    http://www.excelforum.com/member.php...o&userid=28573
    > View this thread: http://www.excelforum.com/showthread...hreadid=494190
    >




  3. #3
    Greg Wilson
    Guest

    RE: naming worksheets

    Try:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$D$1" Then Sh.Name = Target
    End Sub

    Regards,
    Greg

    "jerrystan" wrote:

    >
    > I want to match the name of a worksheet tab with the header of a form.
    > In other words, if I type the name of the form in a header (a
    > cell), the worksheet tab would change the name to match the header
    > name.
    >
    > Here's what I'm using but it's not working:
    >
    > Private Sub workbook_SheetChange(ByVal sh As Object, ByVal target As
    > Range)
    > If target.Address = "$d$1" Then sh.Name = target
    > End Sub
    >
    > Can anyone tell me what I'm doing wrong? THANKS!
    >
    >
    > --
    > jerrystan
    > ------------------------------------------------------------------------
    > jerrystan's Profile: http://www.excelforum.com/member.php...o&userid=28573
    > View this thread: http://www.excelforum.com/showthread...hreadid=494190
    >
    >


  4. #4
    Registered User
    Join Date
    11-04-2005
    Posts
    10
    Thanks guys, but I'm still getting an error. Could my other macros in this file be affecting it??

  5. #5
    Bob Phillips
    Guest

    Re: naming worksheets

    Did you put the code in the ThisWorkbook code module?

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "jerrystan" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Thanks guys, but I'm still getting an error. Could my other macros in
    > this file be affecting it??
    >
    >
    > --
    > jerrystan
    > ------------------------------------------------------------------------
    > jerrystan's Profile:

    http://www.excelforum.com/member.php...o&userid=28573
    > View this thread: http://www.excelforum.com/showthread...hreadid=494190
    >




  6. #6
    Jim Thomlinson
    Guest

    Re: naming worksheets

    Your code will error out if there is already a sheet by that name or if you
    try to use name the sheet "History". You can add this procedure to check if a
    sheet by that name already exists...

    Public Function SheetExists(SName As String, _
    Optional ByVal WB As Workbook) As Boolean
    'Chip Pearson
    On Error Resume Next
    If WB Is Nothing Then Set WB = ThisWorkbook
    SheetExists = CBool(Len(WB.Sheets(SName).Name))
    End Function

    Additionally the code will not catch if anything is pasted into the cell.
    The only code that should affect this is application.enableevents = ?? which
    could effectively turn it off, but it will not cause an error.
    --
    HTH...

    Jim Thomlinson


    "jerrystan" wrote:

    >
    > Thanks guys, but I'm still getting an error. Could my other macros in
    > this file be affecting it??
    >
    >
    > --
    > jerrystan
    > ------------------------------------------------------------------------
    > jerrystan's Profile: http://www.excelforum.com/member.php...o&userid=28573
    > View this thread: http://www.excelforum.com/showthread...hreadid=494190
    >
    >


  7. #7
    Registered User
    Join Date
    11-04-2005
    Posts
    10
    I set up another workbook to test this and it works in there. It must have something to do with my existing code. I checked for duplicate names and the "history" name and its all good. I'll keep messing with it. Thanks for your help.

+ 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