NEED HELP - For run time error '1004'

[Sriram]

Dear Friends,

I'm getting the run time error '1004' - Application defined or Object
defined and some times run time error '1104' - unable to set the locked
property for the range class.

i'm getting this error on the line 37.

here is my code. kindly help me at the earliest.

Workbook Open code:-

Dim I As Integer
Dim J As Integer
Dim LastLine As Integer
Private Sub Workbook_Open()

For I = 4 To 500
If Sheet6.Cells(I, 1).Value = "-" Then
LastLine = I
'MsgBox LastLine
Exit For
End If
Next I

Sheet6.Unprotect Password:="123"

Sheet6.Cells(1, 6).Value = Date
Sheet6.Range(Cells(1, 1), Cells(2, 256)).Locked = True

For I = 3 To LastLine - 1
Sheet6.Cells(I, 7).Locked = True
Next I

For I = 3 To LastLine - 1
For J = 9 To 38
Sheet6.Cells(I, J).Locked = True
Next J
Next I



For I = 3 To LastLine - 1
For J = 9 To 13
If Sheet6.Cells(I, 8).Value = "" Or Sheet6.Cells(I, 8).Value =
"NR" Then
Sheet6.Cells(I, J).Locked = True
Else
Sheet6.Cells(I, J).Locked = False
End If
Next J
Next I

For I = 4 To LastLine Step 2
Sheet6.Cells(I, 9).Locked = True
Sheet6.Cells(I, 10).Locked = True
Next I


Sheet6.Range(Cells(3, 39), Cells(LastLine, 256)).Locked = True
Sheet6.Range(Cells(LastLine, 1), Cells(65536, 256)).Locked = True

Sheet6.Protect Password:="123", AllowFiltering:=True

End Sub

Worksheet Change Code:-

Dim I As Integer
Dim Material As String
Dim Response As String
Dim Msg As String
Dim SiteName As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row Mod 2 = 0 Then
Material = "SHELTER"
SiteName = Cells(Target.Row - 1, 5).Value
Else
Material = "TOWER"
SiteName = Cells(Target.Row, 5).Value
End If

If Target.Column = 8 Then
Me.Unprotect Password:="123"
Select Case (Target)
Case "":
MsgBox "SORRY. YOU SHOULD ENTER EITHER REQ. / NR IN THIS
CELL.", vbCritical, "ERROR"
Case "NR":
Msg = "ARE YOU SURE " & SiteName & "'S " & Material & " IS
NOT REQUIRED?. IF YOU CLICK 'YES' THEN YOU CAN NOT CHANGE THE VALUES BACK
AGAIN."
Response = MsgBox(Msg, vbYesNo, "CONFIRM MATERIAL NOT
REQUIRED")
If Response = vbYes Then
For I = 9 To 38
Cells(Target.Row, I).Value = "NR"
Cells(Target.Row, I).Locked = True
Next I
Else
Target.Value = "REQ."
End If
Case "REQ.":
If Material = "TOWER" Then
Cells(Target.Row, 9).Locked = False
Cells(Target.Row, 9).Value = "-"
Cells(Target.Row, 13).Value = "ND"
Msg = "PLEASE ENTER THE TOWER TYPE."
MsgBox Msg, vbInformation, "ENTER TOWER TYPE"
Else
Cells(Target.Row, 11).Locked = False
Cells(Target.Row, 11).Value = "-"
Cells(Target.Row, 13).Value = "ND"
Msg = "PLEASE ENTER THE SHELTER SUPPLY VENDOR NAME."
MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME"
End If
End Select
Me.Protect Password:="123", AllowFiltering:=True
End If

' If Target.Column = 9 Then
' Me.Unprotect Password:="123"
' Select Case (Target)
' Case "-":
' 'DO NOTHING
' Case "NR":
' If Cells(Target.Row, 8).Value = "REQ." Then
' Target.Value = "-"
' Cells(Target.Row, 10).Value = "-"
' End If
' Case "GBT", "RTT", "POLE", "DELTA":
' Cells(Target.Row, 10).Locked = False
' Cells(Target.Row, 10).Value = "-"
' Msg = "PLEASE ENTER THE TOWER HEIGHT."
' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT"
' Case Else:
' Target.Value = "-"
' Cells(Target.Row, 10).Value = "-"
' End Select
' Me.Protect Password:="123", AllowFiltering:=True
' End If
'
' If Target.Column = 10 Then
' Me.Unprotect Password:="123"
' If Cells(Target.Row, 9).Value = "-" And Target <> "-" Then
' Msg = "PLEASE ENTER THE TOWER TYPE DATA FIRST."
' MsgBox Msg, vbInformation, "DATA UNACCEPTANCE"
' Target.Value = "-"
' Else
' Select Case (Target)
' Case "":
' Target.Value = "-"
' Msg = "PLEASE ENTER THE TOWER HEIGHT."
' MsgBox Msg, vbInformation, "ENTER TOWER HEIGHT"
' Case "-":
' 'DO NOTHING
' Case "NR":
' If Cells(Target.Row, 8).Value = "REQ." Then
' Target.Value = "-"
' Cells(Target.Row, 11).Value = "-"
' End If
' Case Is <= 21:
' If Cells(Target.Row, 9).Value = "GBT" Then
' Msg = "INVALID TOWER HEIGHT."
' MsgBox Msg, vbCritical, "INVALID DATA ENTRY"
' Target.Value = "-"
' Else
' Cells(Target.Row, 11).Value = "-"
' Cells(Target.Row, 11).Locked = False
' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME."
' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME"
' End If
' Case Is > 21:
' If Cells(Target.Row, 9).Value <> "GBT" Then
' Msg = "INVALID TOWER HEIGHT."
' MsgBox Msg, vbCritical, "INVALID DATA ENTRY"
' Target.Value = "-"
' Else
' Cells(Target.Row, 11).Value = "-"
' Cells(Target.Row, 11).Locked = False
' Msg = "PLEASE ENTER THE TOWER SUPPLY VENDOR NAME."
' MsgBox Msg, vbInformation, "ENTER SUPPLY VENDOR NAME"
' End If
' Case Else:
' Target.Value = "-"
' Cells(Target.Row, 11).Value = "-"
' End Select
' End If
' Me.Protect Password:="123", AllowFiltering:=True
' End If
'
' If Target.Column = 11 Then
' Me.Unprotect Password:="123"
' Select Case (Target)
' Case "-":
' 'DO NOTHING
' Case "", " ", "NA":
' Msg = "DON'T FORGET TO ENTER THE SUPPLY VENDOR NAME LATER."
' MsgBox Msg, vbInformation, "INFORMATION"
' Target.Value = "-"
' Case "NR":
' If Cells(Target.Row, 8).Value = "REQ." Then
' Target.Value = "-"
' End If
' Case Else:
' Cells(Target.Row, 12).Locked = False
' Cells(Target.Row, 12).Value = "-"
' Msg = "PLEASE ENTER THE ERECTION VENDOR NAME."
' MsgBox Msg, vbInformation, "ENTER ERECTION VENDOR NAME"
' End Select
' Me.Protect Password:="123", AllowFiltering:=True
' End If
End Sub

Thanks,
Sriram

 

[Bob Phillips]

That's an End If by my calculation. Would you care to be more specific?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sriram]

No Bob,

If there is a problem with end if in compiling itself the error might have
come. if you are not mentioning like this, please tell em clearly.

Sriram

 

[Tom Ogilvy]

Which line of code is highlighted when you get the error?

 

[Tom Ogilvy]

Line 37 would depend on where you start counting. You have posted two
procedures plus some declarations. Saying line 37 is not clear as to which
line is giving the error. The question is, paste a small sub-section of
code that can be uniquely identified and indicate which specific line in that
section is where the error occurs.

 

[Sriram]

Tom u back. Really i'm looking for you.

In worksheet change event see the below codes

if targer.column=8 then
....
....
case"req.":
....
....
CELLS(TARGET.ROW,13).VALUE="ND" - HERE I'M GETTING THE ERROR, mentioning run
time error '1004' - application defined or object defined

else sometimes i'm getting the error run time error '1004' - unable to set
the locked property for the range class, in the line of code...

if target.column=8 then
....
....
if response=vbyes then
....
Cells(Target.Row, I).Value = "NR"
CELLS(TARGET.ROW,I).LOCKED=TRUE

if i marked this line as comment then the above error coming to its above line

CELLS(TARGET.ROW,I).VALUE="NR"

Please help me out.

And one more thing, can you please tell me ur email id or messenger id, so
that we can get in touch mroe and i can send my file also to you, so that you
can go thru clearly.

you can contact me on yahoo msngr or mail in (e-mail address removed) or
hotmail / msn msngr with (e-mail address removed)

Sriram

 

[Tom Ogilvy]

Try modifying your code like this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
Application.EnableEvents = False

' your existing code

ErrHandler:
if err.Number <> 0 then
msgbox "Error " & err.Number & "has occured" &
vbnewline & err.Description
end if
Err.clear
Application.EnableEvents = True
End Sub

 

[Sriram]

Tom,

It is working fine till now.

Now I've come up with one mroe problem. Could you tell me how to find if the
cell's valu is Non Date.

I'm trying this with Select Case event like below,

If Target.Column = 18 Then
Me.Unprotect Password:="123"
Select Case (Target)
Case "":
Cells(Target.Row, 34).Value = "NOT OFFERED"
Case "NR":
If Cells(Target.Row, 8).Value = "REQ." Then
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End If
Case Is < Cells(Target.Row, 17).Value:
Msg = "SORRY. ENTERED DATE SHOULD BE GREATER THAN OR
EQUAL TO THE INTERNAL INSPECTION PASSED DATE." & vbNewLine & " KINDLY ENTER A
NEW DATE IN THIS FORMAT 'MM/DD/YYYY' ."
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
Case Is >= Cells(Target.Row, 17).Value:
Cells(Target.Row, 20).Locked = False
Cells(Target.Row, 34).Value = "NOT INSPECTED"
Case Else:
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End Select
Me.Protect Password:="123", AllowFiltering:=True
End If

Now the problem is, if I entered some text values (means string values, like
A, B or something else), it is taking as such without showing any error and
updating the 34th column as Not Inspected.

I want to find out if the value of the cell is String, then my witten error
msg should dispaly.

Please tell me is there anything available for find string like IsDate,
IsNumeric etc.

Sriram.

 

[Tom Ogilvy]

Look in Excel VBA help at the VarType function.

--
Regards,
Tom Ogilvy

Tom,

It is working fine till now.

Now I've come up with one mroe problem. Could you tell me how to find if
the
cell's valu is Non Date.

I'm trying this with Select Case event like below,

If Target.Column = 18 Then
Me.Unprotect Password:="123"
Select Case (Target)
Case "":
Cells(Target.Row, 34).Value = "NOT OFFERED"
Case "NR":
If Cells(Target.Row, 8).Value = "REQ." Then
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End If
Case Is < Cells(Target.Row, 17).Value:
Msg = "SORRY. ENTERED DATE SHOULD BE GREATER THAN OR
EQUAL TO THE INTERNAL INSPECTION PASSED DATE." & vbNewLine & " KINDLY
ENTER A
NEW DATE IN THIS FORMAT 'MM/DD/YYYY' ."
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
Case Is >= Cells(Target.Row, 17).Value:
Cells(Target.Row, 20).Locked = False
Cells(Target.Row, 34).Value = "NOT INSPECTED"
Case Else:
Msg = "SORRY. ONLY DATE VALUE CAN BE ENTERED. ENTER THE
VALUE IN THE BELOW FORMAT." & vbNewLine & " MM/DD/YYYY "
MsgBox Msg, vbCritical, "INVALID ENTRY"
Target.Value = ""
End Select
Me.Protect Password:="123", AllowFiltering:=True
End If

Now the problem is, if I entered some text values (means string values,
like
A, B or something else), it is taking as such without showing any error
and
updating the 34th column as Not Inspected.

I want to find out if the value of the cell is String, then my witten
error
msg should dispaly.

Please tell me is there anything available for find string like IsDate,
IsNumeric etc.

Sriram.

 

[Sriram]

Tom,

Thanks, Thank You and Thanks a Lot...

Now the time my problem is solved. Because of your support I came out from
my fedups and now I trust that I can complete my project in next 3-4 days.

Thanks a lot my friend.

Somethign Personal:-
I saw your details by clicking your name and really great job Tom. You got
the MVP certification in 2 years after starting posting here. Now I too made
a decision to acheive this MVP in next 6 months by helping more and more new
comers. Wish me for my better than the best growth.

And I got your email id also. Please don't mind if contact you thru email.

Bye & Take Care

Ur Friend
Sriram

Look in Excel VBA help at the VarType function.

 

[Sriram]

Tom,

Please have a look at my below code, where I'm trying to insert new lines
whenever the last but one line data is entered.
(Note I've mentioned LastLine-2, coz I've merged the cells)

It is working very fine and no errors, but What I want here is that,
whenever the lines are getting inserted it is asking a message box on its own
(excel msg box) with the below comments.
"Do you want to replace the contents of the destination cells?" Yes/No.

So, if I enter Yes, no problem it is working fine, if I enter No, and error
occured and it is been captured by your err handler (u gave me previously),
which is showing "Error 1004 occured. PasteSpecial method of Range class
failed."

And again if I enter the data then it is asking the same question, if click
Yes working fine no problem. But if I click No it is showing the error and I
protected the sheet with a Password mentioned in the code, which is Not
Working if I click No.

What I want here is that, is there any method / option to find out and click
the Yes button automatically when the dialog box or msg window is been shown
by excel on its own.

Please have a look at my codes.
------- Code Starts

If Target.Column = 2 Then
LastLine = Cells(2, 40).Value 'In this cell, I entered the lastline
value from the workbook open event itself
'MsgBox "SHEET LSTLIN: " & LastLine
Me.Unprotect Password:="123"
If Target.Row = LastLine - 2 Then
If Cells(Target.Row, Target.Column).Value <> "" Then
'MsgBox LastLine
Range(Cells(Target.Row, 1), Cells(LastLine,
38)).EntireRow.Copy
Range(Cells(LastLine, 1), Cells(LastLine + 2,
38)).PasteSpecial xlPasteAll
Range(Cells(LastLine, 2), Cells(LastLine, 6)).Value = ""
LastLine = LastLine + 2
Cells(2, 40).Value = LastLine
End If
End If
Call RepeatTask
Me.Protect Password:="123", AllowFiltering:=True
End If

------- Code Ends

If you want to know about the code of the function RepeatTask, here it is.

------- Code Begins

Sub RepeatTask()

For I = 3 To LastLine - 1
Cells(I, 40).Value = Cells(I, 37).Value
Next I

End Sub

------- Code Ends

Kindly help me how to find out and click the 'Yes' button whenever excel is
showing a msg box or dialog window.

Thanks

Sriram