• 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.

VBA to unlock reference based on criteria

Emiljano Qorraj

New Member
Hello,
Can anyone please help me with this case?
I am trying to make a vba code to unlock an excel reference only if a cell reference meets a criteria.
I have a sheet with dates in range A2:A32. I want the vba to check if the date in this rage is today's date, then unlock a specific range at the same row.
For example: If A27 is today's date, then unlock B27:K27. All the other cells of the sheet must remain locked.

I will appreciate any help.

Thanks in advance.
 

vletm

Excel Ninja
Emiljano Qorraj
You should reread Forum Rules
especially How to get the Best Results at Chandoo.org
Eg with out eg without a sample Excel-file, You would have more challenges to adopt someone's part of code.
Eg those ranges could modify as UnLocked ... but ... the result would need something else too - or not - if You will track only that option.
Eg All the other cells of the sheet must remain locked. Do that mean, there could be always only that range unlocked?
 

Emiljano Qorraj

New Member
Thank you vletm for the advise! I am new to Chanddo.org Forums i do not know exactly how it works.

I have a file that want to share to others to make data entry, but i want to restrict the users to enter data only in today's date.
The dates are in column A, and i want them to enter data only in the same row reference that correspond the today's date (eg for A27, only in B27:K27) . After they enter data, they can make changes to that reference within that day, after that the reference will be locked and the next date will unlock.
Which means that, i want all the sheet locked and unlock only that reference. The users should not make changes to other dates or other parts of the sheet.

Many thanks for the help!
 

Emiljano Qorraj

New Member
I have tried this code but doesn't work.

>>> use code - tags <<<
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Range("A2:A32") Then
   ' ActiveSheet.Unprotect
    Select Case Target.Value
        Case Date
            Range("B1:Z1").Locked = False
        Case Else
            Range("B1:Z1").Locked = True
    End Select
    ActiveSheet.Protect
    End If
End Sub
 

vletm

Excel Ninja
Emiljano Qorraj
Did You reread those Forum Rules?
as well as
Did You noticed my hint (below)?
Eg with out eg without a sample Excel-file, You would have more challenges to adopt someone's part of code.
 
Top