Naming Ranges with code
On 19 Jan., 12:07, Les wrote:
Hello all, i have a report that has rows added to it and it has both a
columnm that is a range name, so the new cell has to be added to the range
and secondly within that range the cell has it's own name dependant on what
the user puts in.
* * C * * * * * * * * * *D * * * * * E
*Desc. * * * * * * * Code * * * P/O
Bonnet * * * * * * *Hood * * *"HoodPo"Range name
Bumper(Font) * *BF * * * * *"BFPo"
Bumper(Rear) * BR * * * * * "BRPo"
Front end * * * * *FRT * * * *"FRTPo"
IMC-SAS * * * * * IMC * * * *"IMCPo"
New Part * * * * * Test * * * "TestPo"-- New range Name for cell.
The old PORange in column "E" (E1:E5) must expand to (E1:E6)
The "code" will be put in by the user.
--
Les
Hi Les
Put this in the code sheet for the desired worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vName As String
Dim tRange As Range
If Target.Column = 4 Then
vName = Target.Value
Set tRange = Range("E1", Cells(Target.Row, 4))
vName = vName & "Po"
tRange.Name = vName
Target.Offset(0, 1) = vName
End If
End Sub
// Per
|