With Sixthsense' help from above and some tweaking of my own I have solved the riddle.
Thus, any time cell D9 has an entry made to it whether adding in an entry or deleting an entry the tabs will update. When adding an entry the tabs will have the entry added as a prefix to the already existing tab name with a space between the newly added prefix and existing tab name.
Also, when the entry in cell D9 is deleted, the tabs will revert back to their original tab names with the prefix being removed.
Pretty cool ha? Here is the code which needs to be added to the sheet object where cell D9 will be updated in project explorer.
Quote:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sWs As String, sCell As String, shName As String, i As Integer
If Target.Address = "$D$9" And Range("$D$9") <> "" Then
sCell = Range("D9").Value
For i = 2 To 6
sWs = Worksheets(i).Name
On Error Resume Next
shName = sCell & " " & sWs
sWs = shName
Worksheets(i).Name = sWs
On Error GoTo 0
Next i
Else
Worksheets(2).Name = "Add"
Worksheets(3).Name = "Add Remit"
Worksheets(4).Name = "Remove"
Worksheets(5).Name = "Remove Remit"
Worksheets(6).Name = "JE"
End If
End Sub
|