NEED HELP - How to disable a cell to restrict data entry

[Sriram]

Dear Friends,

I need to disable one or more cells, so that the user can not enter any data
in those field(s), when the respective cell's value meets some condition.

For example, if the column heading is say "Pending Job" and its entries will
be either "Pending" or "Completed". This will be selected by the user in the
form of List, so he can't enter anything other than these two. Once he
selected the cell's value as "Completed", then that particular cell should be
Disabled, so that in future he can't change it again to "Pending".

Kindly help me how to achieve this by means of Macro codings, because I amde
the whole file's data and its results are controlled by Macro codings.

Kindly help me ASAP.

Thanks and Regards,
Sriram S

 

[NickHK]

Sriram,
Does Data Validation serve you OK ?

NickHK

 

[Tom Ogilvy]

Sounds like you are already familiar with the Change Event.

(right click on the sheet tab and select view code, then place this in the
resulting module if you don't have one or add similar code to your existing
change event.)

Private Sub Worksheet_Change(ByVal Target As Range)
If target.count > 1 then exit sub
if target.column = 3 then
if trim(lcase(target.value)) = "completed" then
me.unprotect password:="ABC"
target.locked = True
me.Protect password:="ABC"
end if
End if
End Sub

Since your sheet will be protected, make sure that any cells that you want
to allow entries in are formatted as Unlocked. Change Column = 3 to
reflect the column number of the column where you want this behavior.

 

[Sriram]

Dear Tom,

Thanks for your prompt reply. I'm trying for that and let you know about its
functionality soon.

Now I've come up to you with another query. Kindly help me out ATE.

I want the last line of the sheet to go down automatically if its previous
line is entered with data.

For example, my last line is in 20th row which will be filled with "*"
(stars). If the user enters in the first column in the 19th row then
automatically the 20th row having "* stars should go down to 21st row and the
20th row should get blank and should be ready for the user to enter the data.

Again if the user enters the data in the first column of the 20th row, then
the 21st row (which contains "*" stars) shouldgo down one row below and the
21st row should be blank and ready for the user to enter data.

Kindly help me to achieve this ASAP.

Thanks and Regards,
Sriram

 

[Sriram]

Dear Tom,

I want to know how to send the current working excel file using Microsoft
Outlook or some other means as an attachment to my email id automatically
whenever the file is being Closed.

Kindly let me know how to achieve this using excel macros.

Thanks and Regards,
Sriram Subramaniyan

 

[Sriram]

Dear Tom,

I'm getting this run time error '1004' - application defined or object
defined error and some times
run timr error '1004' - unable to set the locked property for the range class

Please Help.

here is my code.

On Wrokbook Open :-

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

On Worksheet Change :-
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

Please Help me at the earliest. Also if you wish give me your email id so
that I can get in touch with you more easy way.

My email id is (e-mail address removed)

Sriram