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

Locking/Unlocking cells based on value

I

ilo_junior

New Member
Dear All,

Need help how to lock or unlock cells based on value from current or other cell? for example, I have list of validation on cell A1 : On going, On hold, and Completed. If the value in A1 is "Completed", the cell A1 is locked. And so on other cell (A2~A...)

Thanks & Rgds
 
Dear Narayan,

Thanks for the shared file. I can lock the cell when the value in column A is "Completed", but the locked cell that I want is not all other cell, just cell in the same row with "Completed" value.

I have some code, hope you can simplified this.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range

    On Error GoTo haveError

    'see if any changes are in the monitored range...
    Set rng = Application.Intersect(Target, Me.Range("A1:A10"))

    If Not rng Is Nothing Then
    'Next line prevents code updates from re-triggering this...
    '  (Not really needed if you're only adding comments)
    Application.EnableEvents = False
    For Each cell In rng.Cells
        If cell.Value = "Completed" Then
            If Target.Offset(0, 1).Locked = False Then
            ActiveSheet.Unprotect "xxx"
            cell.Offset(0, 1).Locked = True
            ActiveSheet.Protect "xxx"
        End If
        End If
    Next
    Application.EnableEvents = True

    End If

    Exit Sub

haveError:
    MsgBox Err.Description
    Application.EnableEvents = True

Thanks & Rgds
 
Hi ,

I think I should explain what I have done !

I have unlocked cells in the range A1 through A20 , and used the check for whether the cell value is Completed to lock the cell which has this value entered in it.

It is incidental that all the other cells in the worksheet were not unlocked. If you unlock all the cells in the worksheet , and then protect the sheet , then the procedure will lock only that cell in column A where the value Completed has been selected from the drop-down.

Can you explain what you want done ? Do you want to lock cells in column A or in column B ? The code you hlave posted is using a column offset of 1.

Secondly , the code you have posted is checking for a range of cells ; this is not necessary , since a Worksheet_Change event procedure traps a change to even a single cell ; if you are selecting values in column A using a Data Validation drop-down , this kind of coding is not needed.

If you can explain what you wish to do , I can modify the code I have already posted to do it , instead of going through some other code and changing it.

Narayan
 
Dear Narayan,

Thanks for the explanation. the code I just post to you is just what I found on the internet with little modification. and I just know that there are any unnecessary code. Really appreciated if you can help me with my purpose.

Here I attach the related file.
The goal is to protect column H6:H25 if the value is "Completed". In the process, if user select "Completed" value there will be asked a question (Yes/No Msgbox) "Are you sure". If yes, the cell will locked and if No, the cell remain no change.

Hope you can help me figured it out.


Thanks & Rgds
Junior
 

Attachments

  • Weekly Activity Log Report (macro).xlsm
    44.6 KB · Views: 52
Dear Narayan,

A Nice, Simple, and effective coding you make. Thank a lot.
May I contact you if I have problem any further?

Thanks & Regards
Junior
 
NARAYANK991 said:
Hi ,

Sure. You can always post your problem in this forum , or get in touch with me specifically.
Click to expand...

Hi
I am new to excel in my file attached I would basically like cells B5:DU5 to automatically lock if DY5 is greater then R0.00 this is on the November 2014 master booking tab i would then like to carry this down so what i want to acheive is that no more bookings can be taken until the DY column for a person has a nil value hope this makes sense really struggling with this.
 

Attachments

  • WILGERS Master Booking Sheet.xlsm
    870.9 KB · Views: 14
Hi Jacque ,

You can also attach your file on any public file-sharing website such as DropBox , GoogleDocs , SkyDrive , and then post the access link here.

Narayan
 
NARAYANK991 said:
Hi Jacques ,

You can also attach your file on any public file-sharing website such as DropBox , GoogleDocs , SkyDrive , and then post the access link here.

Narayan
Click to expand...
Just up loaded if any thing does not make sense please let me know I forgot to mention I protect the sheet and workbook as well so the idea if someone ows you money after a certain amount of bookings they will not be allowed to book until monies are received I really appreciate your quick response.
 
Hi Jacque ,

Please post the access link here ; before you post the link , ensure you have given others permission to download the file(s).

Narayan
 
Hi Jacque ,

When you upload a file to any public file-sharing website , the file cannot be accessed by others till you give them the access link.

So after uploading your file to say DropBox , you have to click on the Share button ; DropBox will then paste the access link to the clipboard. You need to copy that access link and post it here in this thread. Using that link , others can now download your file.

Narayan
 
NARAYANK991 said:
Hi Jacque ,

When you upload a file to any public file-sharing website , the file cannot be accessed by others till you give them the access link.

So after uploading your file to say DropBox , you have to click on the Share button ; DropBox will then paste the access link to the clipboard. You need to copy that access link and post it here in this thread. Using that link , others can now download your file.

Narayan
Click to expand...
Hi I uploaded here it is the Wilgers master booking I had it small enough so did not need to use drop box etc should I upload again ?
 
Hi Jacque ,

Thank you for uploading your file ; suppose you want cells in columns B through DU to be locked when ever the corresponding cells in column DY becomes greater 0.

The amounts in column DY are through a formula , and formula changes cannot be trapped , except that we can use the Worksheet_Calculate event procedure ; this will execute each time any cell in the worksheet is changed.

Suppose the cells in any particular row are locked because the corresponding cell in column DY has gone above 0 , as and when it again goes below 0 , should the locked row be unlocked ?

Narayan
 
NARAYANK991 said:
Hi Jacque ,

Thank you for uploading your file ; suppose you want cells in columns B through DU to be locked when ever the corresponding cells in column DY becomes greater 0.

The amounts in column DY are through a formula , and formula changes cannot be trapped , except that we can use the Worksheet_Calculate event procedure ; this will execute each time any cell in the worksheet is changed.

Suppose the cells in any particular row are locked because the corresponding cell in column DY has gone above 0 , as and when it again goes below 0 , should the locked row be unlocked ?

Narayan
Click to expand...
 
This looks great can I copy and past this VB into all worksheets I add on?
also will it work for as many rows I add on.
And then the message that pops up and I able to change what this message tells the user?
You have no idea how much you are helping me.
 
Hi Jacque ,

If you want to use the same code on several worksheets , you need not copy + paste the code for all of those worksheets ; instead we can make use of the Workbook_SheetCalculate event procedure , which will only one copy , but which can work on all the worksheets where you want this macro to work.

Can you tell me which are the worksheets you want this macro to work on ?

I can do what ever is required , and in case you add more worksheets in future , you can make the necessary additions to the code.

Narayan
 
Hello Narayan,

Great blog, great answers!

I have a similar issues, and the "weekly activity log" sheet can be used to explain it.

Once the status in the log is set to "COMPLETE' the cell is locked. That is great. Is it also possible to lock the entire row, instead of just the cell?

I hope you can help with this.

Best regards,
Arnold
 

Attachments

  • Copy of Weekly Activity Log Report (macro).xlsm
    51.4 KB · Views: 28
You must log in or register to reply here.
Back
Top