Update Worksheet Name Base on Cell Input Automatically?

  • Thread starter Thread starter Native
  • Start date Start date
N

Native

Hello,

I have the following code in a worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(external:=True) _
= Range("p2").Address(external:=True) Then
On Error Resume Next
Me.Name = Target.Value
On Error GoTo 0
End If
End Sub

My question is, how do I get the worksheet name to update
automatically? For example, the cell P2 has a lookup based on an input
in cell B3. When I change B3, P2 looks up the text and changes from
blue to say green. However, when I change B3, the worksheet name does
not automatically update.

The only way I can get the worksheet name to update is to open the
cell P2 and hit enter.

Any suggestions?

Thanks
 
If you're using a formula in P2, you should use the worksheet_Calculate event.

Option Explicit
Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Name = Me.Range("P2").Value
If Err.Number <> 0 Then
Beep
Err.Clear
End If
 
Back
Top