#1
|
|||
|
|||
VBA Unprotect Input Date By Offset
Please need help to solve the programming :
unprotect sheet if the cell is selected and protect sheet again after the run vba completed. The column 2 is for the input "IN" and protect the column 4, but if I insert command "Sheet1.Unprotect" then the Vba won't work. Quote:
|
#2
|
|||
|
|||
See if this helps (I have not tested this macro):
http://stackoverflow.com/questions/5...ated-worksheet |
#3
|
|||
|
|||
Thank you Logit for reply, I have opened your link and try to modify.
But still protected in column 4. Any suggestion ? |
#4
|
|||
|
|||
This works down to Row 4, although the code should be good down to Row 100.
Code:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim inputRange As Range Set ws = Worksheets("Sheet1") 'tell this sub to unprotect only these cells Set inputRange = Range("B2:B100,C1:C100") ' If the selected cell is not in the range keep the sheet locked If Intersect(Target, inputRange) Is Nothing Then 'else unprotect the sheet by providing password '(same as the one that was used to protect this sheet) Else ws.Unprotect Password:="" Target.Locked = False With Target If .Cells.Count = 1 Then If .Column = 2 And .Row > 1 And .Value = "In" Then .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss") End If If .Column = 3 And .Row > 1 And .Value = "Out" Then .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss") End If End If End With ws.Protect Password:="" End If End Sub See what you can do with it. Steps: Paste the code. Protect Sheet1 (no password). Start B1 and go down entering In. Same for C1 |
#5
|
|||
|
|||
@ genpromo
How about you attach a sample sheet with your code and the same cell locking and sheet protection as you're wanting us to suggest a solution for. Then we can test things on the same thing you're using, no guessing as to what's what. To attach a sheet click Go Advanced > Manage Attachments |
#6
|
|||
|
|||
Believe this is the solution ... Changed the macro slightly :
New Code : Code:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim inputRange As Range Set ws = Worksheets("Sheet1") ws.Unprotect Application.EnableEvents = False 'tell this sub to unprotect only these cells Set inputRange = Range("B1:C100") ' If the selected cell is not in the range keep the sheet locked If Intersect(Target, inputRange) Is Nothing Then 'else unprotect the sheet by providing password '(same as the one that was used to protect this sheet) Else Target.Locked = False With Target If .Cells.Count = 1 Then If .Column = 2 And .Row > 1 And .Value = "In" Then .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss") End If If .Column = 3 And .Row > 1 And .Value = "Out" Then .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss") End If End If End With End If Application.EnableEvents = True ws.Protect End Sub Choose the PROTECTION TAB. Make certain LOCKED is UNCHECKED. (Because you highlighted the two Cols B&C, this 'unlocked setting' will only apply to these two columns). Click OK, etc. Now, go to the MENU BAR up top / REVIEW / PROTECT SHEET. Protect the sheet without providing a password - leave that blank. The macro should now work as desired. If you choose to utilize a password the macro will need to be edited to include that password in the code. The code as presently written applies to rows 1 to 100. |
#7
|
|||
|
|||
@Logit : Thank you for the code, very much appreciated for your help. Do not know what to do without this forum
@Nosparks : here is the file after the code change from Logit. This file for attendance employee, so they can absence by themselves but can not change the attendance record. |
#8
|
|||
|
|||
Glad to help.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unprotect Cells | Cardinal2 | Excel Programming | 2 | 01-06-2015 05:15 PM |
Set offset properties of a picture | charm | PowerPoint | 1 | 11-01-2014 05:41 AM |
Formula In Excel To Prompt User To Input Date | pclutts | Excel Programming | 4 | 08-31-2014 12:45 PM |
Syntax to use OFFSET() in a cell formula? | dlowrey | Excel | 3 | 07-09-2013 09:15 AM |
ADD in That can unprotect the documents. | aligahk06 | Word | 0 | 07-12-2010 04:55 AM |