Chewyhairball
Active Member
- Joined
- Nov 30, 2017
- Messages
- 312
- Office Version
- 365
- Platform
- Windows
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
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
VBA Code:
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
VBA Code:
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