none
Rename a sheet when cell value of another sheet changes RRS feed

  • Question

  • I have a workbook with several sheets. Sheet1 is the summary sheet, and the other sheets should derive their names from the summary sheet. The I would like to change the name of other sheets based on the value of certain cells in the summary sheet. I have tried the following code, but am getting 'Runtime error 1004. Application-defined or object-defined error. 

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Address = "$B$7" Then
        If Target.Value = "" Then
         Sheets(2).Name = "test Sheet"
        Else
            Sheets(3).Activate
        Worksheets(3).Name = Target.Value
        End If
    End If
    
    End Sub
    Worksheets(3).Name = Target.Value is giving me the error

    I'm just trying to teach myself how to use VBA in excel, and can use some advice.

     
    Saturday, April 6, 2019 9:12 PM

Answers

  • If the workbook is protected, you could use

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$B$7" Then
            ThisWorkbook.Unprotect Password:="secret"
            On Error Resume Next
            Worksheets(3).Name = Target.Value
            If Err Then
                Worksheets(3).Name = "Test Sheet"
            End If
            ThisWorkbook.Protect Password:="secret", Structure:=True
        End If
    End Sub

    Replace "secret" with the password that you used.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, April 7, 2019 12:19 PM

All replies

    1. You should use the Worksheet_Change event instead of the Worksheet_SelectionChange event.
    2. Why do you refer to Sheets(2) in the If part, and to Worksheets(3) in the Else part?
    3. There is no need to activate a sheet to rename it.
    4. Some characters aren't allowed in sheet names, for example / \ * : ?

    See if this does what you want:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$B$7" Then
            On Error Resume Next
            Worksheets(3).Name = Target.Value
            If Err Then
                Worksheets(3).Name = "Test Sheet"
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, April 6, 2019 9:32 PM
    1. You should use the Worksheet_Change event instead of the Worksheet_SelectionChange event.
    2. Why do you refer to Sheets(2) in the If part, and to Worksheets(3) in the Else part?
    3. There is no need to activate a sheet to rename it.
    4. Some characters aren't allowed in sheet names, for example / \ * : ?

    See if this does what you want:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$B$7" Then
            On Error Resume Next
            Worksheets(3).Name = Target.Value
            If Err Then
                Worksheets(3).Name = "Test Sheet"
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    This code executes, but makes no changes to the sheet name
    Saturday, April 6, 2019 10:33 PM
  • Do you manually edit the value of B7?

    The code won't work if B7 contains a formula.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, April 7, 2019 7:55 AM
  • B7 is a dropdown list. However the code is still giving the same error, but the on error allows it to run. The problem was the workbook was protected.
    Sunday, April 7, 2019 8:03 AM
  • If the workbook is protected, you could use

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$B$7" Then
            ThisWorkbook.Unprotect Password:="secret"
            On Error Resume Next
            Worksheets(3).Name = Target.Value
            If Err Then
                Worksheets(3).Name = "Test Sheet"
            End If
            ThisWorkbook.Protect Password:="secret", Structure:=True
        End If
    End Sub

    Replace "secret" with the password that you used.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, April 7, 2019 12:19 PM
  • Thank you Hans! That was my next question.
    Sunday, April 7, 2019 4:33 PM