Worksheet_Change - Multiple Changes - Code not working ?????

[spydey]

I have a worksheet with a Worksheet_Change sub in it.

I have two sets of code in it.

Each set, individually, works as planned (tested). But when together, for some reason only the first set is catching correctly.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

''Change worksheet name


    If Target.Cells.Count > 1 Or Intersect(Target, Range("B2")) Is Nothing Then Exit Sub


Application.EnableEvents = False
    
    If IsEmpty(Target.Value) Then
        Application.ActiveSheet.Name = "Template"
    Else
        If IsNumeric(Target.Value) Then
            Application.ActiveSheet.Name = Target
        End If
    End If
    
Application.EnableEvents = True


''Calculate number of days


    If Target.Cells.Count > 1 Or Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
        
Application.EnableEvents = False
        
        If IsEmpty(Target.Value) Then
            Target.Offset(2, 0).ClearContents
        Else
            If IsDate(Target.Value) Then
                Target.Offset(2, 0) = Application.WorksheetFunction.Days360(Range("B3"), Date, False)
            End If
        End If
    
Application.EnableEvents = True


   


End Sub

I believe the issue of the 2nd set of code not catching the changes to B3 stems from the first set of code's 1st line:

If Target.Cells.Count > 1 Or Intersect(Target, Range("B2")) Is Nothing Then Exit Sub

As I understand it, if the changed cell is not B2, then the sub exits, thus the code for B3 never runs.

Is that right?

I see the issue (I believe), but I am not sure about how to solve it.

Any thoughts, ideas, pointers?

Maybe if I change the 1st line to something like:

If Target.Cells.Count > 1 Or Intersect(Target, Range("B2")) Is Nothing Then GoTo CalculateDays

And have the second set start like this:

''Calculate number of days
CalculateDays:
    If Target.Cells.Count > 1 Or Intersect(Target, Range("B3")) Is Nothing Then Exit Sub

I am just not sure if that is the best way to do it.

What would the best way to make the code simpler, better, more efficient, not as sloppy (hahaha), etc.

I don't think the use of GoTo is very efficient.

Ideas? Thoughts? Suggestions? Pointers?

-Spydey

As always, your assistance and patience is very very much appreciated!

 

[spydey]

Ok, So I just tested the "GoTo'" idea and it worked.

My concern is that I may, in the future, have more than 2 sections of code, meaning, more than 2 cells to actively watch.

I don't want to have to write the same code over and over and over again, and I don't want to have to utilize "GoTo" all the time. Just seems ugly to me. Unless that really is the best way to do it for this particular scenario .....

So I need to figure out a better way to code this and have it open to future additions of more cells/ranges to watch.

Ideas?

-Spydey

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

''Change worksheet name


    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B2")) Is Nothing Then
      
         Application.EnableEvents = False
             
             If IsEmpty(Target.Value) Then
                 Application.ActiveSheet.name = "Template"
             Else
                 If IsNumeric(Target.Value) Then
                     Application.ActiveSheet.name = Target
                 End If
             End If
             
         Application.EnableEvents = True
      

''Calculate number of days


   ElseIf Not Intersect(Target, Range("B3")) Is Nothing Then
        
      Application.EnableEvents = False
              
              If IsEmpty(Target.Value) Then
                  Target.Offset(2, 0).ClearContents
              Else
                  If IsDate(Target.Value) Then
                      Target.Offset(2, 0) = Application.WorksheetFunction.Days360(Range("B3"), Date, False)
                  End If
              End If
          
      Application.EnableEvents = True
   End If
End Sub

 

[spydey]

@ Fluff
You always seem to catch my threads ..... thank you!!! You have assisted me on more than one occasion and I really appreciate it.

So if I follow suit, and need to watch additional cells for changes, I would simply add an ElseIf and then the code, right?

I will give it a try.

Thanks again Fluff!

-Spydey

 

[Fluff]

As ever, glad to help & thanks for the feedback

So if I follow suit, and need to watch additional cells for changes, I would simply add an ElseIf and then the code, right?

That's right