|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I would like to have the name tab of a worksheet change to the entry in
a cell.
I have multiple sheets that all reference a master sheet. The master
sheet is the place where the user sets up the information for the other
sheets.
There are cells in the master setup sheet, where the user enters the
names of individual pieces of equipment. Each worksheet contains data
pertaining to one piece of equipment.
I found some VB code on Excel Tips and Tricks that works, but it only
renames the active sheet from a cell within it's own sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub
I could use this and link a cell in the individual equipment's sheets
to the master sheet to get the name to the equipment sheets, but this
VB code only works on the active sheet. I would want it to update all
the tabs when names are entered in the master sheet, regardless of
whether the equipment sheets are active or not.
This workbook is used as template for many different groups of
equipment that are constantly changing, thus I cannot create a static
template for each group of equipment.
I hope this makes sense.
|
|
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each sh In ActiveWorkbook.Worksheets
i = i + 1
sh.Name = Cells(i, "A").Value
Next sh
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
<mmel[ at ]systron.com> wrote in message
news:1158778237.944267.279050[ at ]h48g2000cwc.googlegroups.com...
[Quoted Text] > I would like to have the name tab of a worksheet change to the entry in
> a cell.
> I have multiple sheets that all reference a master sheet. The master
> sheet is the place where the user sets up the information for the other
> sheets.
> There are cells in the master setup sheet, where the user enters the
> names of individual pieces of equipment. Each worksheet contains data
> pertaining to one piece of equipment.
> I found some VB code on Excel Tips and Tricks that works, but it only
> renames the active sheet from a cell within it's own sheet.
> Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
> Set Target = Range("A1")
> If Target = "" Then Exit Sub
> On Error GoTo Badname
> ActiveSheet.Name = Left(Target, 31)
> Exit Sub
> Badname:
> MsgBox "Please revise the entry in A1." & Chr(13) _
> & "It appears to contain one or more " & Chr(13) _
> & "illegal characters." & Chr(13)
> Range("A1").Activate
> End Sub
> I could use this and link a cell in the individual equipment's sheets
> to the master sheet to get the name to the equipment sheets, but this
> VB code only works on the active sheet. I would want it to update all
> the tabs when names are entered in the master sheet, regardless of
> whether the equipment sheets are active or not.
> This workbook is used as template for many different groups of
> equipment that are constantly changing, thus I cannot create a static
> template for each group of equipment.
> I hope this makes sense.
>
|
|
Thanks for the response, Bob.
Questions:
1) Do I have to put this into each worksheet's code module?
2) Is the 'string' the range of cells that the equipment names are
entered into on the master sheet?
Mike
Bob Phillips wrote:
[Quoted Text] > Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "A:A" '<=== change to suit
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> For Each sh In ActiveWorkbook.Worksheets
> i = i + 1
> sh.Name = Cells(i, "A").Value
> Next sh
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <mmel[ at ]systron.com> wrote in message
> news:1158778237.944267.279050[ at ]h48g2000cwc.googlegroups.com...
> > I would like to have the name tab of a worksheet change to the entry in
> > a cell.
> > I have multiple sheets that all reference a master sheet. The master
> > sheet is the place where the user sets up the information for the other
> > sheets.
> > There are cells in the master setup sheet, where the user enters the
> > names of individual pieces of equipment. Each worksheet contains data
> > pertaining to one piece of equipment.
> > I found some VB code on Excel Tips and Tricks that works, but it only
> > renames the active sheet from a cell within it's own sheet.
> > Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
> > Set Target = Range("A1")
> > If Target = "" Then Exit Sub
> > On Error GoTo Badname
> > ActiveSheet.Name = Left(Target, 31)
> > Exit Sub
> > Badname:
> > MsgBox "Please revise the entry in A1." & Chr(13) _
> > & "It appears to contain one or more " & Chr(13) _
> > & "illegal characters." & Chr(13)
> > Range("A1").Activate
> > End Sub
> > I could use this and link a cell in the individual equipment's sheets
> > to the master sheet to get the name to the equipment sheets, but this
> > VB code only works on the active sheet. I would want it to update all
> > the tabs when names are entered in the master sheet, regardless of
> > whether the equipment sheets are active or not.
> > This workbook is used as template for many different groups of
> > equipment that are constantly changing, thus I cannot create a static
> > template for each group of equipment.
> > I hope this makes sense.
> >
|
|
No, just put it with the master sheet. Any time you change a value in the
list of sheet names, it will go and update them. Do leave A1 as the master
sheet name though.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
<mmel[ at ]systron.com> wrote in message
news:1158960655.565545.148560[ at ]b28g2000cwb.googlegroups.com...
[Quoted Text] > Thanks for the response, Bob.
> Questions:
> 1) Do I have to put this into each worksheet's code module?
> 2) Is the 'string' the range of cells that the equipment names are
> entered into on the master sheet?
> Mike
>
> Bob Phillips wrote:
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "A:A" '<=== change to suit
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > For Each sh In ActiveWorkbook.Worksheets
> > i = i + 1
> > sh.Name = Cells(i, "A").Value
> > Next sh
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <mmel[ at ]systron.com> wrote in message
> > news:1158778237.944267.279050[ at ]h48g2000cwc.googlegroups.com...
> > > I would like to have the name tab of a worksheet change to the entry
in
> > > a cell.
> > > I have multiple sheets that all reference a master sheet. The master
> > > sheet is the place where the user sets up the information for the
other
> > > sheets.
> > > There are cells in the master setup sheet, where the user enters the
> > > names of individual pieces of equipment. Each worksheet contains data
> > > pertaining to one piece of equipment.
> > > I found some VB code on Excel Tips and Tricks that works, but it only
> > > renames the active sheet from a cell within it's own sheet.
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
> > > Set Target = Range("A1")
> > > If Target = "" Then Exit Sub
> > > On Error GoTo Badname
> > > ActiveSheet.Name = Left(Target, 31)
> > > Exit Sub
> > > Badname:
> > > MsgBox "Please revise the entry in A1." & Chr(13) _
> > > & "It appears to contain one or more " & Chr(13) _
> > > & "illegal characters." & Chr(13)
> > > Range("A1").Activate
> > > End Sub
> > > I could use this and link a cell in the individual equipment's sheets
> > > to the master sheet to get the name to the equipment sheets, but this
> > > VB code only works on the active sheet. I would want it to update all
> > > the tabs when names are entered in the master sheet, regardless of
> > > whether the equipment sheets are active or not.
> > > This workbook is used as template for many different groups of
> > > equipment that are constantly changing, thus I cannot create a static
> > > template for each group of equipment.
> > > I hope this makes sense.
> > >
>
|
|
|