View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
Default how can I make a cell content equal the tab name 'sheet1' ?

Event code from Bob Phillips to change tab to cell contents.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Adjust "A1" to suit then Alt + q to go back
to the Excel window to test by entering a value in A1


Gord Dibben MS Excel MVP


On Sat, 5 Apr 2008 13:47:00 -0700, balder
wrote:



"Mike H" wrote:
It worked!! Thank you very much. Another challenge perhaps - how to make a
tab name equal the content of a spreadsheet cell?

Rune

Try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Mike

"balder" wrote:

Is there a way to make a cell content in a spreadsheet equal to the tab name
of that spread sheet w/o having to type the tab name into the cell?
Like b1="tab name" where tab name would be copied into cell b1