Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like the tab name in a spreadsheet to change depending on the value of a cell in one of the tabs. Is this possible?

    thanks

    Alan

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Is this on Every sheet, OR Just a specific sheet?

    If a specific sheet, then something like this can go in the Sheets Private Module

    NOTE.. I have given the Changing Cell a Range Name ChangeCell rather than using its Address

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim strInvalid As String, intI As Integer, fOK As Boolean
    Dim strBadChar As String
    fOK = True
    strInvalid = "/\*?[]"
    
    'Check IF the Cell Changed WAS the Cell that Effects the Tab
    If Target.Address = Range("ChangeCell").Address Then
        'Check it is NOT Blanks and Not over 31 Characters
        If Target <> "" And Len(Target) <= 31 Then
            'Check for Invalid Characters
            For intI = 1 To Len(strInvalid)
                If InStr(1, Target, Mid(strInvalid, intI, 1)) <> 0 Then
                    strBadChar = Mid(strInvalid, intI, 1)
                    MsgBox "Tab name Has Invalid Character ....  " & strBadChar, vbExclamation
                    fOK = False
                    Exit For
                End If
            Next
            If fOK Then
                ActiveSheet.Name = Target.Value
            End If
        Else
            MsgBox "Over 31 Characters, cannot be used as a Sheet Name", vbExclamation
        End If
    End If
    
    End Sub

    Note.. This does not check if there is another sheet with the same name.
    Andrew

  3. #3
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's for any tab.

    Basically, I have a list of application names in a sheet called "variables". The app name will change depending on the application. Then, for each application, there is a tab where the user will enter bits of data which get summed up in a Summary tab.

    It would be a "nice to have" if the application name could be shown on each tab.

    I guess I could use your code and loop round for each tab in question. I was hoping there was a "simple" way that I had missed.

    thanks

    Alan

Posting Permissions

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