Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    name sheet (Excel 2000 (9.0.2720))

    Hi and thanks in advance. Does anyone know of a simple way, macro maybe, to have a sheet be named based on the contents of a cell. For example, if cell A5 contains "Fred", the sheet will automatically be named Fred.
    Appreciate any and all help.

    Not a very knowledgable or experienced user. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: name sheet (Excel 2000 (9.0.2720))

    Right-click the sheet name and select "view code"
    paste this code into the code pane

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

    Steve

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: name sheet (Excel 2000 (9.0.2720))

    Thanks for the information. As a newbie, I likely left out some important information. The name I would like the sheet to be named, is from a selection from a combo box, that has been put into the sheet using a control. The control utilizes a list from a separate sheet (controls) that allows the use to select the name of a sport. I would like the sheet to be called the sport. Does that make it any clearer than mud? Again, thanks as I try to learn how to do things.

    Fred
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: name sheet (Excel 2000 (9.0.2720))

    Steve's code is still what you need, you just need to change all references to cell A5 or a5 in his code to the cell that the combobox puts the name into. Again, Steve's code MUST go in the module behind the sheet by right clicking on the sheet tab and clicking on "View code", not into a normal module.
    Legare Coleman

Posting Permissions

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