Hi all,
I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.
The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc.
I have the following code in the ThisWorkbook section:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim sOldName As String
Application.ScreenUpdating = False
sOldName = ActiveSheet.Name
If Target.Address <> "$B$1" Then Exit Sub
sName = ActiveSheet.Range("B1")
On Error GoTo ErrorHandler
ActiveSheet.Name = sName
On Error GoTo 0
Sheet8.Select 'this is the overview sheet
Cells.Find(What:=sOldName, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Value = sName
Sheets(sName).Select
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox "Sheet Name " & Target.Value & " cannot be blank, already exists or is an invalid name.", vbCritical, "Error"
Application.EnableEvents = False
ActiveSheet.Range("B1") = ActiveSheet.Name
Application.EnableEvents = True
End Sub
Display More
And this is where I'm having trouble...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then Call UpdateLine1Name
If Target.Address = "$C$5" Then Call UpdateLine2Name
If Target.Address = "$C$6" Then Call UpdateLine3Name
End Sub
Sub UpdateLine1Name()
Dim oldName As String
Dim newName As String
newName = Range("C4").Value
With Sheet27
oldName = .Range("B1").Value
End With
With Sheet8
.Range("C4").Value = oldName
End With
With Sheet27
.Range("B1").Value = newName
End With
End Sub
Display More
Any help would be appreciated as I think I have fried my brain on this!
Cheers,
Averil