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

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" wrote in message
...
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

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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.

--
Regards,
Tom Ogilvy


"Sriram" wrote:

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

--
Regards,
Tom Ogilvy


"Sriram" wrote:

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

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 or
hotmail / msn msngr with

Sriram

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Sriram" wrote:

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

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

--
Regards,
Tom Ogilvy


"Sriram" wrote:

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 or
hotmail / msn msngr with

Sriram

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Sriram" wrote:

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

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" wrote:

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

--
Regards,
Tom Ogilvy

Look in Excel VBA help at the VarType function.

--
Regards,
Tom Ogilvy


"Sriram" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy