Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - How to disable a cell to restrict data entry
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - How to disable a cell to restrict data entry
Sriram,
Does Data Validation serve you OK ? NickHK "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - How to disable a cell to restrict data entry
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. -- Regards, Tom Ogilvy "Sriram" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - How to disable a cell to restrict data entry
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Sriram" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP - How to disable a cell to restrict data entry
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 ---- "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED HELP-Run Time Err '1004' Application defined or Object Define
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 Sriram |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrict data entry in a cell | Excel Worksheet Functions | |||
lock cell conditional format but not restrict data entry | Excel Worksheet Functions | |||
How can I restrict a cell(s) to alpha-only data entry? | Excel Discussion (Misc queries) | |||
Restrict Data Entry to only 4 possible entries | Excel Programming | |||
can i restrict data entry from certain columns | Excel Discussion (Misc queries) |