runtime error #1004

[VectorW2]

I am getting this application error; however, I don't understand because the cells do exist. I do have a drop down menu, that I added that you can start typing and it will auto type the rest of text, "works great", it jump the sheet number count however from single digits to triple digits. Is this the problem? if so, how do I fix it?

When I test run the macro, the this part of the code is blocked red with red dots in margin, so I relatively sure the problem lays here, complete code below as well.

[code]
For Each rngArea In Range("A3,f3,f4,A5,A7,a508:c508,a509:c509,a510:b510,A512:F512," & _
"a513:f513,c514,d514,c515,d515,c516,d516,a518:B518,a520:f521,a522:f522," & _
"A523:f525,a527:b527,c527,d527,e527,f527,a528:b528,c528,d528,e528," & _
"f528,a529:b529,c529,d529,e529,f529,a530:b530,c530,d530,e530," & _
"f530,a531:b531,c531,d531,e531,f531,a532:b532,c532,d532,e532," & _
"f532,a534:b534,c534,d534,e534,f534,a535:b535,c535,d535,e5235," & _
"f535,a537:f537,a539:f540,a541:f541,a542:f543,a545:f545,a546:f546," & _
"a547:f549,a550:e550,a551:e551,a552,a553:f553,a554,a555:b555,a556:b556," & _
"a557,a560,a561,a562,a563,a565").Areas
/[code]

[code]
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngArea As Range, ws As Worksheet

If Target.Address(0, 0) <> "M1" Then Exit Sub

If LCase(Target.Value) = "finish" Then

ActiveSheet.Unprotect Password:="pw"

For Each rngArea In Range("A3,f3,f4,A5,A7,a508:c508,a509:c509,a510:b510,A512:F512," & _
"a513:f513,c514,d514,c515,d515,c516,d516,a518:B518,a520:f521,a522:f522," & _
"A523:f525,a527:b527,c527,d527,e527,f527,a528:b528,c528,d528,e528," & _
"f528,a529:b529,c529,d529,e529,f529,a530:b530,c530,d530,e530," & _
"f530,a531:b531,c531,d531,e531,f531,a532:b532,c532,d532,e532," & _
"f532,a534:b534,c534,d534,e534,f534,a535:b535,c535,d535,e5235," & _
"f535,a537:f537,a539:f540,a541:f541,a542:f543,a545:f545,a546:f546," & _
"a547:f549,a550:e550,a551:e551,a552,a553:f553,a554,a555:b555,a556:b556," & _
"a557,a560,a561,a562,a563,a565").Areas

rngArea.Value = rngArea.Value
Next rngArea

With Range("A508:c508,M1").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

With Range("A508:c508,d15,M1").Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0

End With

With Range("A508:c508").Borders(xlEdgeBottom).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeTop).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeRight).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeLeft).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeBottom).LineStyle = x1linestylenone

End With

Range("A5,A508:c508,c514:d514,C515:d515, c516:d516").Locked = True

With Range("m1").Font
.TintAndShade = 0

End With

With Range("M1").Clear

End With

Application.Goto Range("A1"), Scroll:=True

With Sheets("Summary Sheet")
.Unprotect Password:="pw"
.Range("a5").Value = .Value
.Range("a5").Locked = True
.Range("A8:F17").Locked = True
.Range("A8:F17").FormulaHidden = True
.Protect Password:="pw"
.EnableSelection = xlUnlockedCells
'Application.Goto .Range("A8:F17")
End With

'runit
For Each ws In Sheets(Array("AVF", "LUF", "MVF", "NTDD", "TDD"))
If ws.Visible Then
ws.Unprotect Password:="pw"
ws.Range("A7:F17").Locked = True
ws.Range("A8:F17").FormulaHidden = True
ws.Protect Password:="pw"
ws.EnableSelection = xlUnlockedCells
End If
Next ws

With Sheets("Letter")
.Protect Password:="pw"
.EnableSelection = xlUnlockedCells
End With

ChDrive "G:\Forms & Lists"
ChDir "G:\Forms & Lists"
ActiveWorkbook.SaveAs Filename:= _
"Please re-name me.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'Withint
ElseIf LCase(Target.Value) = "reveal" Then
Sheets("Info").Visible = True
Sheets("ci2").Visible = True
Sheets("Info").Select

End If

End Sub
/[code]

Thank you in advance for any and all help!

VectorW2

[skywriter]

Something going on with your code tags, they didn't work.

When I test run the macro, the this part of the code is blocked red with red dots in margin, so I relatively sure the problem lays here, complete code below as well.

That sounds like a break point. You could try Control + Shift + F9 and if it's a break point it should disappear.

It doesn't explain the 1004, but if they aren't break points then we need to find out what they are.

[VectorW2]

Thanks Skywriter, I will try that and let you know what happens.

[VectorW2]

Now the red highlight is gone, it's yellow with the message Range("a3,f3,f4,A5,a508....=<method 'range of object '_worksheet' failed>. I've looked up a few fixes, like adding "ws." before range or ".range(.cell" before the cell list, but none of these seem to do the trick, either got the same message or the issue got worse.

VectorW2

[skywriter]

It would be helpful if you could re-post your original code again with the code tags. If we can get the code tags to work then we are assured that anyone copying your code gets it in the original format.

What I usually do is in Excel I highlight my code, then copy it. I then click the # icon which gives you ##, I then put my cursor between the ## and use Control + V to paste my code between the ##'s.

If you could try that again it would be great. You can paste all you code into one entry you don't have to break it up into separate procedures, like you did in your first post.

[VectorW2]

No problem,

[code]
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngArea As Range, ws As Worksheet

If Target.Address(0, 0) <> "M1" Then Exit Sub

If LCase(Target.Value) = "finish" Then

ActiveSheet.Unprotect Password:="pw"

For Each rngArea In Range("A3,f3,f4,A5,A7,a508:c508,a509:c509,a510:b510,A512:F512," & _
"a513:f513,c514,d514,c515,d515,c516,d516,a518:B518,a520:f521,a522:f522," & _
"A523:f525,a527:b527,c527,d527,e527,f527,a528:b528,c528,d528,e528," & _
"f528,a529:b529,c529,d529,e529,f529,a530:b530,c530,d530,e530," & _
"f530,a531:b531,c531,d531,e531,f531,a532:b532,c532,d532,e532," & _
"f532,a534:b534,c534,d534,e534,f534,a535:b535,c535,d535,e5235," & _
"f535,a537:f537,a539:f540,a541:f541,a542:f543,a545:f545,a546:f546," & _
"a547:f549,a550:e550,a551:e551,a552,a553:f553,a554,a555:b555,a556:b556," & _
"a557,a560,a561,a562,a563,a565").Areas

rngArea.Value = rngArea.Value
Next rngArea

With Range("A508:c508,M1").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

With Range("A508:c508,d15,M1").Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0

End With

With Range("A508:c508").Borders(xlEdgeBottom).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeTop).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeRight).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeLeft).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeBottom).LineStyle = x1linestylenone

End With

Range("A5,A508:c508,c514:d514,C515:d515, c516:d516").Locked = True

With Range("m1").Font
.TintAndShade = 0

End With

With Range("M1").Clear

End With

Application.Goto Range("A1"), Scroll:=True

With Sheets("Summary Sheet")
.Unprotect Password:="pw"
.Range("a5").Value = .Value
.Range("a5").Locked = True
.Range("A8:F17").Locked = True
.Range("A8:F17").FormulaHidden = True
.Protect Password:="pw"
.EnableSelection = xlUnlockedCells
'Application.Goto .Range("A8:F17")
End With

'runit
For Each ws In Sheets(Array("AVF", "LUF", "MVF", "NTDD", "TDD"))
If ws.Visible Then
ws.Unprotect Password:="pw"
ws.Range("A7:F17").Locked = True
ws.Range("A8:F17").FormulaHidden = True
ws.Protect Password:="pw"
ws.EnableSelection = xlUnlockedCells
End If
Next ws

With Sheets("Letter")
.Protect Password:="pw"
.EnableSelection = xlUnlockedCells
End With

ChDrive "G:\Forms & Lists"
ChDir "G:\Forms & Lists"
ActiveWorkbook.SaveAs Filename:= _
"Please re-name me.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'Withint
ElseIf LCase(Target.Value) = "reveal" Then
Sheets("Info").Visible = True
Sheets("ci2").Visible = True
Sheets("Info").Select

End If

End Sub

/[code]

I was wondering if the drop down menu I mentioned in merged cells A508:c508, where this issue start could be the root of the problem. the drop down works well. but looking at the code you can see it was a long list to scroll through. so I added the auto spell, also working well, but reference the sheet "ci2". Is this my issue, do I some how need to tie the code back to the formula in the cell instead of the sheet the cell is in?

VectorW2

[VectorW2]

Please Login or Register  to view this content.

[VectorW2]

With your guidance, the code posted correctly any, we are that much closer.

[skywriter]

What is happening is you just have too many ranges in the range reference. I did a little experimentation to see if there's a syntax error in there but I find none, so I'm convinced it's too much.

What I did was split it in half and run the loop twice.

Also the part of the code I fixed runs fine on my computer.

This code changes the cells so I turned off the events so that it doesn't keep looping every time a cell is changed. I also added error handling in case the code I fixed errors on your computer you will get a message box saying there's still an error in that code. I added error handling for the rest of the code. It's only going to give you a description of the code and the error number, so you can give me that info if it happens and maybe I can figure it out from that. If not you're going to have to step through the code and figure out where the next error is.

Please Login or Register  to view this content.

[VectorW2]

It seems to have gotten us past the issue, now, in edit code on line "On error goto err_handler2" the error is (Compile error - label not defined

Please Login or Register  to view this content.

[VectorW2]

scrub that, I didn't copy correctly, let me try again. I'll get back to you

[VectorW2]

ok - a ran it and the message is: "There was an error with that same code."

[skywriter]

Well this could go back and forth and get nowhere. You need to post the workbook.

ViewPic

[VectorW2]

Ok, paperclip appeared and I think I attached file, please let me know if you have it or any questions.
Thanks!