dsheard2015
Board Regular
- Joined
- May 10, 2016
- Messages
- 134
Hello, I am in the process of transferring a lot of the sheet calculations and conditional formatting into vba for efficiency and in the process I am receiving an error that I cannot seem to figure out, "run-time error 1004; method range of object worksheet failed". I have attached all the worksheet code for this sheet, the error is coming from the worksheet change event code and the line in red text is where the error is according to the error message. I will also try to attach an image of the worksheet so you can see the layout. Any help is greatly appreciated.
Thank you very much!
Thank you very much!
VBA Code:
Private Sub Worksheet_Activate()
Dim rLockable As Range
Dim x As Range
Dim y As Range
Dim z As Range
Dim c As Range
'Dim cell As Range
'Dim cl As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim aer As AllowEditRange
Dim sh As Worksheet
On Error Resume Next
On Error GoTo 0
Set sh = ThisWorkbook.ActiveSheet
Set rng1 = Range("M12:V50, AI15:AR50, M65:V103, AI68:AR103, M118:V156, AI121:AR156, M171:V209, AI174:AR209, M224:V262, AI227:AR262, M277:V315, AI280:AR315, M330:V368, AI333:AR368, M383:V421, AI386:AR421, M436:V474, AI439:AR474")
Set rng2 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474, X15:X50, X68:X103, X121:X156, X174:X209, X227:X262, X280:X315, X333:X368, X386:X421, X439:X474")
Set rng3 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474")
Set rng4 = Range("X15:X50, X68:X103, X121:X156, X174:X209, X227:X262, X280:X315, X333:X368, X386:X421, X439:X474")
Set x = Range("BC:BW")
Set y = Range("BY:CS")
Set z = Range("W:AR")
StopCalc
Unprotect Password:=Sheet577.Range("H27").Value
For Each c In rng2
If c.Value <> Empty Then
c.Interior.Color = RGB(221, 217, 196)
c.Font.Color = RGB(0, 0, 0)
c.Font.Bold = False
Else
c.Interior.Color = RGB(255, 255, 255)
c.Font.Color = RGB(0, 0, 0)
End If
Next c
For Each c In rng3
If Range("WS15").Value = True And Range("WT15").Value = False Or Range("WS15").Value = True And Range("WT15").Value = True And Range("WW15").Value > 0 Then
Range("B15").Interior.Color = RGB(0, 0, 0)
Range("B15").Font.Color = RGB(255, 0, 0)
Range("B15").Font.Bold = True
If Range("RP15").Value = "w" Then
Range("B15").Interior.Color = RGB(255, 255, 255)
Range("B15").Font.Color = RGB(0, 0, 0)
Range("B15").Font.Bold = False
End If
End If
Next c
If Range("X15").Value > 0 Then
z.EntireColumn.Hidden = False
Shapes("Form7LogoB").Visible = True
Else: z.EntireColumn.Hidden = True
Shapes("Form7LogoB").Visible = msoFalse
End If
If Range("BY15").Value > 0 Then
y.EntireColumn.Hidden = False
Shapes("Form7LogoD").Visible = True
Shapes("UnsatTaskMsg").Visible = True
Else: y.EntireColumn.Hidden = True
Shapes("Form7LogoD").Visible = msoFalse
Shapes("UnsatTaskMsg").Visible = msoFalse
End If
If Range("BC15").Value > 0 Then
x.EntireColumn.Hidden = False
Shapes("Form7LogoC").Visible = True
Shapes("UnsatTaskMsg").Visible = True
Else: x.EntireColumn.Hidden = True
Shapes("Form7LogoC").Visible = msoFalse
Shapes("UnsatTaskMsg").Visible = msoFalse
End If
For Each aer In Protection.AllowEditRanges
aer.Delete
Next aer
Protection.AllowEditRanges.Add Title:="Grade Info", Range:=Range("M12:V50,AI15:AR50,M65:V103,AI68:AR103,M118:V156,AI121:AR156,M171:V209,AI174:AR209,M224:V262,AI227:AR262,M277:V315,AI280:AR315,M330:V368,AI333:AR368,M383:V421,AI386:AR421,M436:V474,AI439:AR474"), Password:=Sheets("OVERRIDE PASSWORD").Range("G14").Value
For Each c In rng1
If Sheet278.Range("AJ1").Value = 1 Then
c.Locked = False
ElseIf Sheet278.Range("AJ1").Value = 2 Then
c.Locked = True
ElseIf Sheet278.Range("AJ1").Value = 3 And c.Value = "" Then
c.Locked = False
ElseIf Sheet278.Range("AJ1").Value = 4 Then
c.Locked = True
ElseIf Sheet278.Range("AJ1").Value = 5 Then
c.Locked = True
Else
c.Locked = True
End If
Next c
Range("C930").Value = 1
Protect Password:=Sheet577.Range("H27").Value
ResetCalc
Range("A1").Select
Range(Range("RT7").Value).Select
If Range("A2").Value = True Then
CarryOn = MsgBox("Is there any ammo or rockets that need to be entered for this training flight?", vbYesNo, "Ammo & Rockets")
If CarryOn = vbYes Then
Rockets_Bullets1.Show
If CarryOn = vbNo Then
GoTo Cancel
End If
End If
End If
StopCalc
Unprotect Password:=Sheet577.Range("H27").Value
Range("XH23").Value = Range("XJ21").Value
Range("XH28").Value = Range("XJ26").Value
Range("XI31").Value = Range("XJ31").Value
Range("WU15:WU50").Value = Range("CW15:CW50").Value
Range("WU68:WU103").Value = Range("CW15:CW50").Value
Range("WU121:WU156").Value = Range("CW15:CW50").Value
Range("WU174:WU209").Value = Range("CW15:CW50").Value
Range("WU227:WU262").Value = Range("CW15:CW50").Value
Range("WU280:WU315").Value = Range("CW15:CW50").Value
Range("WU333:WU368").Value = Range("CW15:CW50").Value
Range("WU386:WU421").Value = Range("CW15:CW50").Value
Range("WU439:WU474").Value = Range("CW15:CW50").Value
Protect Password:=Sheet577.Range("H27").Value
ResetCalc
Cancel:
Protect Password:=Sheet577.Range("H27").Value
End Sub
[COLOR=rgb(147, 101, 184)]
Private Sub Worksheet_Change(ByVal c As Range)
Dim rng1 As Range 'page 1 grades
Dim rng2 As Range 'page 2 grades
Dim rng3 As Range 'previous page 1 grades
Dim rng4 As Range 'previous page 2 grades
Dim rng5 As Range 'page 1 task numbers
Dim rng6 As Range 'page 2 task numbers
Dim rng7 As Range 'WS column
Dim rng8 As Range 'WT column
Dim rng9 As Range 'WW column
Set rng1 = Range("M15:V50,M68:V103,M121:V156,M174:V209,M227:V262,M280:V315,M333:V368,M386:V421,M439:V474")
Set rng2 = Range("AI15:AR50,AI68:AR103,AI121:AR156,AI174:AR209,AI227:AR262,AI280:AR315,AI333:AR368,AI386:AR421,AI439:AR474")
Set rng3 = Range("BN15:BW50,BN68:BW103,BN121:BW156,BN174:BW209,BN227:BW262,BN280:BW315,BN333:BW368,BN386:BW421,BN439:BW474")
Set rng4 = Range("CJ15:CS50,CJ68:CS103,CJ121:CS156,CJ174:CS209,CJ227:CS262,CJ280:CS315,CJ333:CS368,CJ386:CS421,CJ439:CS474")
Set rng5 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474")
Set rng6 = Range("X15:X50,X68:X103,X121:X156,X174:X209,X227:X262,X280:X315,X333:X368,X386:X421,X439:X474")
Set rng7 = Range("WS15:WS50")
Set rng8 = Range("WT15:WT50")
Set rng9 = Range("WW15:WW50")
If Not Intersect(c, rng1) Is Nothing Then
StopCalc
Unprotect Password:=Sheet577.Range("H27").Value
Range("XI23").Value = Range("XJ21").Value
Range("WV15:WV50").Value = Range("CW15:CW50").Value
Range("WV68:WV103").Value = Range("CW15:CW50").Value
Range("WV121:WV156").Value = Range("CW15:CW50").Value
Range("WV174:WV209").Value = Range("CW15:CW50").Value
Range("WV227:WV262").Value = Range("CW15:CW50").Value
Range("WV280:WV315").Value = Range("CW15:CW50").Value
Range("WV333:WV368").Value = Range("CW15:CW50").Value
Range("WV386:WV421").Value = Range("CW15:CW50").Value
Range("WV439:WV474").Value = Range("CW15:CW50").Value
Protect Password:=Sheet577.Range("H27").Value
ResetCalc
End If
If Not Intersect(c, rng1) Is Nothing Then
Unprotect Password:=Sheet577.Range("H27").Value
For Each c In rng5
If Range(Cells(c.Row + 615).Value).Value = True And Range(Cells(c.Row + 616).Value).Value = False Or Range(Cells(c.Row + 615).Value).Value = True And Range(Cells(c.Row + 616).Value).Value = True And Range(Cells(c.Row + 619).Value).Value <> Empty Then 'change task number to black
c.Interior.Color = RGB(0, 0, 0)
c.Font.Color = RGB(255, 0, 0)
c.Font.Bold = True
If Range(Cells(c.Row + 484).Value).Value = "w" Then
c.Interior.Color = RGB(255, 255, 255)
c.Font.Color = RGB(0, 0, 0)
c.Font.Bold = False
End If
End If
If Range(Cells(c.Row + 616).Value).Value = True And Range(Cells(c.Row + 619).Value).Value <> Empty Then 'unsat given after sat
Unprotect Password:=Sheet577.Range("H27").Value
Range(Cells(c.Row + 53).Value).Value = True
c.Interior.Color = RGB(255, 0, 0)
c.Font.Color = RGB(0, 0, 0)
c.Font.Bold = False
Else
Unprotect Password:=Sheet577.Range("H27").Value
Range(Cells(c.Row + 53).Value).Value = False
c.Interior.Color = RGB(255, 255, 255)
c.Font.Color = RGB(0, 0, 0)
c.Font.Bold = False
End If
Next c
Protect Password:=Sheet577.Range("H27").Value
End If
If Not Intersect(c, rng2) Is Nothing Then
Unprotect Password:=Sheet577.Range("H27").Value
Range("XI23").Value = Range("XJ21").Value
Range("XB15:XB50").Value = Range("CX15:CX50").Value
Protect Password:=Sheet577.Range("H27").Value
End If
If Not Intersect(c, rng3) Is Nothing Then
Unprotect Password:=Sheet577.Range("H27").Value
Range("XI28").Value = Range("XJ26").Value
Protect Password:=Sheet577.Range("H27").Value
End If
If Not Intersect(c, rng4) Is Nothing Then
Unprotect Password:=Sheet577.Range("H27").Value
Range("XI28").Value = Range("XJ26").Value
Protect Password:=Sheet577.Range("H27").Value
End If
End Sub
Private Sub Worksheet_Deactivate()
Unprotect Password:=Sheet577.Range("H27").Value
Range("WV15:WV50,XB15:XB50").ClearContents
Protect Password:=Sheet577.Range("H27").Value
End Sub