• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unlocking a Cell if certain criteria is meet.

Carley

New Member
Little background - I am in the process of creating a productivity spreadsheet, so when a resource chooses a certain activity the times are automattically populated from pre-agreed timings spreadsheet.


However, there are some activities that the resource will need to add the times themselves. Like Training and Meetings


Is there a code that if the activity matches "Away from Desk Work Activity" either the locked timing cell opens allowing them to add there own times, or a pop up box appears asking them to enter the time which is then populated into the locked cell?


Tried the following formula but didn't react


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("F:F").Value = "Away from Desk Work Activity" Then

Range("E:E").Locked = False

Else

Range("E:E").Locked = True

End If

End Sub
 
Hi, Carley!

Try this:

-----

[pre]
Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' constants
Const ksInput = "E:E"
Const kiControl = 1
Const ksText = "Away from Desk Work Activity"
' start
If Application.Intersect(Target, Range(ksInput)) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
' process
With Target
If .Offset(0, kiControl).Value = ksText Then
.Locked = False
Else
.Locked = True
End If
End With
' end
End Sub
[/pre]
-----

Regards!
 
I want to do a time study. If i give Ctrl+T the start time should be given.and if i give Ctrl + t the end time should be capture. Please suggest the VB code for this
 
@mani88music

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!
 
Thank you @SirJB7 that works!


I was going round in circles!


Out of interest, is there anything I can to to get the cell / Sheet to re-lock once the time is entered into the cell by the user?
 
Hi, Carley!


Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


Regarding last question, the previous code is for the change selection event. You' d try something similar for the change event (same syntax) changing the If statement withing the With Target group to:

-----

Code:
If IsDate(.Value) Then .Locked = True

-----


But there you'll face the issue of how to modify an entered value once inputted a valid date field (date means date & time), I think you'll require a command button to reset all initial values.


Regards!
 
Back
Top