Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi all....I'm sure that I have seen a bunch of stuff written on this topic but I cannot find it in the forum.......I have a WB with 20-30 sheets, names Master, Master(2), Master(3) etc etc

    I need a formula or worksheet change event VBA code that will change the sheet name and tab name to be whatever value is entered manually into cell N8..........thanks.

  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
    Not quite sure what you mean by sheet name and tab name.
    The name on the tab is the sheet name.

    You can use the Workbook SheetChange event for this.
    It checks for changes to any sheet.
    It does not check for changes to formulas though.

    You have to remember with this, that there are restrictions

    You have to check Worksheet and Chart names

    Also check the entry for Blank, Invalid Characters, and also for being too long.

    The example code below covers most of this.

    You MUST have the option lines at the top.
    One checks invalid variable typing and the other makes sure that the comparisons are not case sensitive

    Code:
    Option Compare Text
    Option Explicit
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim wks As Worksheet, chs As Chart
    Dim strName As String, fFound As Boolean
    Dim strChar As String, intChar As Integer
    
    If Target.Address = "$N$8" Then
        If Target <> "" Then
            'Now Test for Invalid Character
            fFound = False
            For intChar = 1 To Len(Target)
                strChar = Mid(Target, intChar, 1)
                If InStr(":\/?*[]", strChar) <> 0 Then
                    fFound = True
                    MsgBox "Sheet Name " & Target & " contains invalid Character " & strChar, vbExclamation
                    Exit For
                End If
            Next
            If fFound = True Then Exit Sub
            'Then check length is not over 31
            If Len(Target) > 31 Then
                MsgBox "Cannot have a sheet Name more than 31 characters" & vbLf & "Name in cell is " & Len(Target), vbExclamation
                Exit Sub
            End If
            'Now check that this name is not used elsewhere
            fFound = False
            For Each wks In Worksheets
                If Target = wks.Name Then
                    fFound = True
                    strName = wks.Name
                    MsgBox "Sheet " & strName & " already exists.", vbExclamation
                    Exit For
                End If
            Next
            If fFound = True Then Exit Sub
            'Also check it is not a Chart sheet
            fFound = False
            For Each chs In Charts
                If Target = chs.Name Then
                    fFound = True
                    strName = chs.Name
                    MsgBox "Chart Sheet " & strName & " already exists.", vbExclamation
                    Exit For
                End If
            Next
            If fFound = True Then Exit Sub
            'If we get here then we can change the sheet name
            Sh.Name = Target
        End If
    End If
    
    End Sub
    Andrew

  3. #3
    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
    How about something like this?:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("n8")) Is Nothing Then
    	On Error Resume Next
    	ActiveSheet.Name = Range("n8")
      End If
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew - a question...

    In your code you have


    If Target.Address = "$N$8" Then


    I am curious why the IF since the user has already said he wanted N8 to contain the sheet name. Also, how would VB be able to determine if the Target.Address = "$N$8"?

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The IF Target.Address="$N$8" is so the Code Block NOT get Processed if other cells are changed on the sheet
    You don't want it to happen if C5 is changed.
    The Target is a parameter passed to the Private Sub By Excel.
    It is the changed Cell
    So if P9 was changed Excel would pass P9 as a Range Object

    When we receive the Range called Target, we need to check that it was N8 that was altered

    The $N$8 is because by default Address Property returns an Absolute Address
    Andrew

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got it. thank you.

Posting Permissions

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