name tab from cell value
Hi Denise,
The simplest way to handle that, and a better way, is to make your name
cells on each sheet named ranges (Insert | Name | Define). For example,
if the cell is named "DataSheet" on the sheet, you could enter the
following in its module:
Option Explicit
Private Sub Worksheet_Calculate()
On Error GoTo ErrorTrap
If Range("DataSheet").Value < Me.Name Then
Me.Name = Range("DataSheet").Value
End If
Exit Sub
ErrorTrap:
MsgBox "New sheet name in range DataSheet " & _
"contains invalid characters."
End Sub
Then you can enter the same code in another sheet's module, replacing
"DataSheet" with whatever the defined name is for that particular
sheet's "name cell". Great catch on the illegal characters, so it now
traps errors. There could be other errors, like the range name not
existing, or misspelling the name in the code, but I assume you'd catch
that in testing.
Gary
|