Update Worksheet Name Base on Cell Input Automatically?
 

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

Update Worksheet Name Base on Cell Input Automatically?
 

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
On Error GoTo 0
End Sub

Native wrote:

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

--

Dave Peterson