Search code examples
excelvba

Run Macro When Reference Cell Updated

I have an Excel Macro Workbook with two Sheets : 'Company' & 'Company News'. I have written a macro for the 'Company' sheet where text in a cell within the worksheet is used to update the name of the worksheet. For instance, if I change the text in Company!B3 to "House", the worksheet is automatically renamed "House". The code I've used is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B3")) Is Nothing Then

    If Range("B3") = Empty Then
        ActiveSheet.Name = "Company Unspecified"
    Else
        ActiveSheet.Name = Range("B3")
    End If
    
End If

End Sub

In the second worksheet, 'Company News', Cell 'Company News'!C4 references Cell Company!B3 through the equation =Company!B3 . When I change the name in Company!B3, the 'Company' worksheet name changes and the value in 'Company News'!C4 changes to the same value in Company!B3.

What I would like is for the name of the 'Company News' sheet to change along with the 'Company' sheet name when the value in Company!B3 is altered. For instance, if I change the text in Company!B3 to "House", 'Company' sheet name should change to 'House' and 'Company News' should change to 'House News'.

I've tried a couple different possibilities with no luck :

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C4")) Is Nothing Then

    If Range("C4") = Empty Then
        ActiveSheet.Name = "Company Unspecified"
    Else
        ActiveSheet.Name = Range("C4")
    End If
    
End If

End Sub

AND ...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Worksheets("Sheet1").Range("B3").Value) Is Nothing Then

    If Worksheets("Sheet1").Range("B3").Value = Empty Then
        ActiveSheet.Name = "Company Unspecified"
    Else
        ActiveSheet.Name = Worksheets("Sheet1").Range("B3").Value
    End If
    
End If

End Sub

I hope my question is clear.

Solution

  • You could do it like this, using the other sheet's CodeName to refer to it:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nm As String
    If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
        nm = Trim(Me.Range("B3").Value)
        If Len(nm) = 0 Then nm = "Company Unspecified"
        
        Me.Name = nm                 'change this sheet's tab name
        NewsTab.Name = nm & " News"  ' `NewsTab` is the *codename* of the "news" sheet
    End If
End Sub

    CodeName: https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htm

    Note you may want to add some code to handle the case where a company name might have characters not allowed in a tab name. https://bettersolutions.com/excel/worksheets/naming.htm#:~:text=They%20must%20obviously%20be%20unique%20within%20the%20workbook%20and%20cannot%20contain%20any%20of%20the%20following%20characters%20%5C%20%2C%20/%20%2C%20*%20%2C%20%3F%20%2C%20%3A%20%2C%20%5B%20%2C%20%5D.