Problems with If-Then-Else in data change sub-routine

[arfmmf]

Hi All,


I program in many different languages but am new to VBA. I have a workbook with 16 worksheets in it. WorksheetA has a range of cells (B6:B20) which are user input cells which, among other things, are used to rename the tabs for 15 other worksheets, 1 per cell. I want to create a worksheet change subroutine which triggers anytime any of the 15 cells values change. I found some code which comes close but no cigar. That code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6")) Is Nothing Then
JBox2.Name = ActiveSheet.Range("B6")
End If
Code repeated for each of the 14 other cells B7 thru B20
End Sub

I simplified it to:

Private Sub Worksheet_Change(ByVal Target As Range)
'Rename Worksheet tab names
If Range("B6").Select <> "" Then
WorksheetB.Name = ActiveSheet.Range("B6")

End If
Code repeated for each of the cells B7 thru B20
End Sub

The simplified code seems to works the same as the original, changing the tab name as entered. This works fine for my needs (no need to check for too long entry’s, etc.) except in the case when any of the cells B6 thru B20 is cleared of data. In that case, instead of the code jumping over the "Then" code and landing on “End If” it errors out and the debugger highlights my “Then” code.

In the case of null data I want to insert data from a different cell set to keep Excel from generating an error and leaving the tab named whatever it was before the data was deleted. I have tried the following code inserted where the blank line is above (along with text in cell B21, etc. but I still get the error. Code to be inserted as else code:

Else: WorksheetB.Name = ActiveSheet.Range("B21")
Code repeated for each of the 14 other cells (B22 thru B35)

When the Error is displayed, the Debugger always points to the WorksheetB.Name = ActiveSheet.Range("B6") line of code instead of the Else line, which seems strange as when the data is null it should be jumping over that line of code and not even evaluating that line of code.

Does the Debugger always point at the actual line of code it is erroring on or does it sometimes point to the line before, or after, something I have seen in other debuggers?

This seems like it should be a fairly simple set of code but I have tried many different ways and have not found what does all that I am looking for.

Thanks for your help.

 

[mart37]

Maybe something like this:

Dim Oldname As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B20")) Is Nothing Then
Oldname = Target
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim fOld As Object
Dim fNew As Object
If Not Intersect(Target, Range("B6:B20")) Is Nothing Then
    If Target <> "" Then
        On Error Resume Next
        Set fOld = Worksheets(Oldname).Range("A1")
        Set fNew = Worksheets(Target.Value).Range("A1")
        On Error GoTo 0
        If Not fOld Is Nothing And fNew Is Nothing Then Worksheets(Oldname).Name = Target
    End If
End If
Set fOld = Nothing
Set fNew = Nothing
End Sub