item with specified name not found

[Chewyhairball]

Hi

I have two bit of code that work perfectly well together. One is Sub worksheet_change and the other is Sub Chart17.
Chart 17 is called from the worksheet change and everything works lovely.

Until...........I add a userform!!

Since I have added it i always get the error below referencing ActiveSheet.Shapes("Chart 17").ZOrder msoBringToFront not found.
This line of code is in Sub Chart 17 which if I run by itself (not called from other) it works fine?

I dont really understand what is going on and could do with some help..

P

rivate Sub Worksheet_Change(ByVal Target As Range)
    
    ActiveSheet.Unprotect
    If Range("C13") = "" Then
    Application.ScreenUpdating = False
    ActiveCell.FormulaR1C1 = "'Select"
    End If
    
    Application.ScreenUpdating = False
    If Target.Address = "$C$13" Then
    
    Range("D17").FormulaR1C1 = "=iferror(MIN(Sheet1!R[-13]C#),"""")"
    Range("E17").FormulaR1C1 = "=iferror(MAX(Sheet1!R[-13]C[-1]#),"""")"
    
    ActiveSheet.Unprotect
    With Range("C13:T13").Interior.ThemeColor = xlThemeColorLight2
    End With
    
    With Range("C13:E13").Interior.ThemeColor = xlThemeColorAccent6
    End With
  
    Call Chart17
  
    ActiveSheet.Unprotect
    Range("B5").FormulaR1C1 = _
        "=IF(COUNTIFS(Table1[[#Data],[Result]],"">""&R[-2]C[23],Table1[[#Data],[Result]],"">""&0)+COUNTIFS(Table1[[#Data],[Result]],""<""&R[-1]C[23],Table1[[#Data],[Result]],"">""&0)=0,"""",CONCATENATE(COUNTIFS(Table1[[#Data],[Result]],"">""&R[-2]C[23],Table1[[#Data],[Result]],"">""&0)+COUNTIFS(Table1[[#Data],[Result]],""<""&R[-1]C[23],Table1[[#Data],[Result]],"">""&0),"" Re" & _
        "sults" & Chr(10) & "Outwith" & Chr(10) & "Action" & Chr(10) & "Level""))" & _
        ""
    ActiveSheet.Unprotect
    Range("B6").FormulaR1C1 = "=R[7]C[1]"
    
    ActiveSheet.Unprotect
    Range("B7").FormulaR1C1 = _
        "=CONCATENATE(""Nominal Value  "",if(ROUND(R[-2]C[23],4)=0,"""",ROUND(R[-2]C[23],4)))"
        
        ActiveSheet.Unprotect
    Range("B8").FormulaR1C1 = _
        "=IFERROR(CONCATENATE(""S2        "",IF(ROUND(R[-2]C[23],4)=0,"""",ROUND(R[-2]C[23],4))),""S2"")"
        
        ActiveSheet.Unprotect
    Range("B9").FormulaR1C1 = "=iferror(CONCATENATE(""Bias    "",ROUND(R[-2]C[23],4)),""Bias"")"
    
    ActiveSheet.Unprotect
    Range("B10").FormulaR1C1 = "=CONCATENATE(""CL%    "",if(R[-1]C[23]=0,"""",R[-1]C[23]))"
    
    ActiveSheet.Unprotect
    Range("B11").FormulaR1C1 = _
        "=IFERROR(CONCATENATE(""CV%    "",IF(ROUND(R[-3]C[23],4)=0,"""",ROUND(R[-3]C[23],4))),""CV%"")"
    
    Application.ScreenUpdating = False
    Call UpdateRows
     
    Range("C14:C16").Value = "text here"
      
    Range("C13").Offset(5, 0).End(xlDown).Offset(3, 0).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(7, 0)).Value = "text here"
    Range(ActiveCell.Offset(18, 0), ActiveCell.Offset(21, 0)).Value = "text here"
    Range(ActiveCell.Offset(23, 0), ActiveCell.Offset(24, 0)).Value = "text here"
    
    Range("C13").Select
     
    End If

Sub Chart17()
     
     Application.ScreenUpdating = False
     
      ActiveSheet.Unprotect
      Application.EnableEvents = False
          Range("C13:T13").Select
           Application.EnableEvents = True
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
      
      
      
              Range("C13:E13").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
      
         
        ActiveSheet.Shapes("Chart 17").ZOrder msoBringToFront
        
      
        Range("B5").FormulaR1C1 = _
            "=IF(COUNTIFS(Table1[[#Data],[Result]],"">""&R[-2]C[23],Table1[[#Data],[Result]],"">""&0)+COUNTIFS(Table1[[#Data],[Result]],""<""&R[-1]C[23],Table1[[#Data],[Result]],"">""&0)=0,"""",CONCATENATE(COUNTIFS(Table1[[#Data],[Result]],"">""&R[-2]C[23],Table1[[#Data],[Result]],"">""&0)+COUNTIFS(Table1[[#Data],[Result]],""<""&R[-1]C[23],Table1[[#Data],[Result]],"">""&0),"" Re" & _
            "sults" & Chr(10) & "Outwith" & Chr(10) & "Action" & Chr(10) & "Level""))" & _
            ""
        ActiveSheet.Unprotect
        Range("B6").FormulaR1C1 = "=R[7]C[1]"
        
        ActiveSheet.Unprotect
        Range("B7").FormulaR1C1 = _
            "=CONCATENATE(""Nominal Value  "",if(ROUND(R[-2]C[23],4)=0,"""",ROUND(R[-2]C[23],4)))"
            
            ActiveSheet.Unprotect
        Range("B8").FormulaR1C1 = _
            "=IFERROR(CONCATENATE(""S2        "",IF(ROUND(R[-2]C[23],4)=0,"""",ROUND(R[-2]C[23],4))),""S2"")"
            
            ActiveSheet.Unprotect
        Range("B9").FormulaR1C1 = "=iferror(CONCATENATE(""Bias    "",ROUND(R[-2]C[23],4)),""Bias"")"
        
        ActiveSheet.Unprotect
        Range("B10").FormulaR1C1 = "=CONCATENATE(""CL%    "",if(R[-1]C[23]=0,"""",R[-1]C[23]))"
        
        ActiveSheet.Unprotect
        Range("B11").FormulaR1C1 = _
            "=IFERROR(CONCATENATE(""CV%    "",IF(ROUND(R[-3]C[23],4)=0,"""",ROUND(R[-3]C[23],4))),""CV%"")"
    Call AdjustVerticalAxis
    
    
    
    End Sub

 

[Chewyhairball]

I got it working!!! although I dont understand it.
I removed unprotect and screen updating from the worksheet change

    ActiveSheet.Unprotect
    If Range("C13") = "" Then
    Application.ScreenUpdating = False
    ActiveCell.FormulaR1C1 = "'Select"
    End If

to

  If Range("C13") = "" Then
    ActiveCell.FormulaR1C1 = "'Select"
    End If

The odd thing is both codes work if I have not added a userform but only the updated bottom one works when I have added one.

Another odd thing is there is already a userform in the sheet and this doesnt effect it, it only errors when i add a new one...even if it is blank with no code added to it yet!!!