VBA not working with a formula

[amberdstjohn]

Hi,

I have a VBA code that I am using that changes the tab name when a cell is updated. This works great when you type data into the cell, but I am mainly needing it to work with a formula. The formula is just a vlookup, and I tried using =(CELL("contents",$O$3), but that doesn't work either. Any tips on how to make the code work with a formula?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    Sh.Name = Sh.Range("A1").Value
  End If
End Sub

 

[Domenic]

Try using the Workbook_SheetCalculate event handler instead...

Workbook.SheetCalculate event (Excel)

Office VBA reference topic

learn.microsoft.com

However, you should probably check whether a sheet with the same name already exists before changing the name of the sheet tab. Otherwise you'll get an error if one already exists.

Hope this helps!

 

[amberdstjohn]

Try using the Workbook_SheetCalculate event handler instead...

Workbook.SheetCalculate event (Excel)

Office VBA reference topic

learn.microsoft.com

However, you should probably check whether a sheet with the same name already exists before changing the name of the sheet tab. Otherwise you'll get an error if one already exists.

Hope this helps!

Thanks for this, but it still isn't updating when the formula updates. I am new to using VBA, so it could be something I am doing wrong.

 

[Domenic]

The following code must be placed in the code module called ThisWorkbook. This code will be executed whenever any worksheet is recalculated. First it checks to make that cell A1 does not contain an error. Then it makes sure that it's not blank. And then it makes sure that a sheet with the same name does not already exist. If all true, it renames the worksheet. Otherwise it's ignored. Does this help?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    Dim targetCell As Range
    Set targetCell = Sh.Range("A1")
    
    If Not IsError(targetCell) Then
        If Len(targetCell) > 0 Then
            If Not SheetExists(targetCell.Value, ThisWorkbook) Then
                Sh.Name = targetCell.Value
            End If
        End If
    End If
    
End Sub

Private Function SheetExists(ByVal sSheetName As String, Optional ByVal wb As Workbook = Nothing) As Boolean

    Dim x As Object
    
    If wb Is Nothing Then
        Set wb = ActiveWorkbook
    End If
    
    On Error Resume Next
    Set x = wb.Sheets(sSheetName)
    On Error GoTo 0
    
    SheetExists = Not x Is Nothing
    
End Function