using vba name worksheet same as a cell

  • Thread starter Thread starter Wanna Learn
  • Start date Start date
W

Wanna Learn

Hello
Below is the code I've been using,
now I want to change to code so that instead of a date is the word on cell G1
How do I correct this thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
newname = Application.WorksheetFunction.Text(Range("G1"), "mm-dd-yy")
ActiveSheet.Name = newname
End With
End Sub
 
I'm not sure why you'd have to change the name of the worksheet each time you
changed selection--maybe using the _change event or _calculate event would be
better (depending on how G1 changed).

If it's changed by typing:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, Me.Range("G1")) Is Nothing Then
Exit Sub
End If

On Error Resume Next
Me.Name = Format(Target.Value, "mm-dd-yy")
'or maybe this if the cell is already displaying
'the name you want to use
'me.name = Target.text
If Err.Number <> 0 Then
Beep 'rename failed
Err.Clear
End If
On Error GoTo 0

End Sub

VBA has the equivalent of the worksheet function =text().

I used the Me keyword. That represents the object that owns the code. In this
case, it's the worksheet that's getting the change. (Depending on your code (in
other procedures??), the activesheet isn't always the sheet that owns the code.)
 
Personally, I'd deal with it using the Worksheet_Change() event rather than
the SelectionChange() event. Every time that you select a new cell or group
of cells, your current routine is being called. It really only needs to be
called when the contents of G1 changes. This code will do that for you:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$1" Then
ActiveSheet.Name = Range("G1").Value
End If
End Sub

Just delete your existing code entirely and put the code above in the same
worksheet code module where the old code was.
 
Back
Top