fiqs8sjn5by0n

Locking cell values dynamically

By rose | January 6, 2009

  • I have a spreadsheet with data about individuals organised in rows. The speadsheed is basically a list of people attending a social function - contact details, and whether they have paid or not. I would like to be able to have a function, whereby if someone has already stated that they are not coming (shown on the list by selecting "No" from a dropdown) the cell asking whether they've paid is greyed out, and the contents is locked. I can manage the greying out with conditional formatting. Is there also a way to lock the contents of a cell (ghosting out the option) depending on the value of a different cell in the same row.

    Thanks

    Doug


  • No, I don't want to lock the cell which is changed - that must stay editable to enable people to change their mind.

    So, for example if column a was the cell asking if people were attending or not, and columns b - e were columns containing various data eg name, contact details, payment status etc. If column a is set to "no" - ie the person is not attending, I would like the columns b - e locked, and unable to be edited. If however column A is set to "yes" and the person is due to attend, I would like columns b - e to be editable. I also need to be able to change column a from yes to no and back again freely.

    I am running XL XP - so perhaps I can use sheet protection as you say...

    Doug


  • Sure there is. Although I'm a little confused about the ...

    lock the contents of a cell (ghosting out the option)

    What do you mean by that, 'ghosting out the option'? Or do you just want to lock the cell? If it's wanting to change the format of a cell, you'll need VBA. If it's wanting to change what is displayed in a cell (for the most part) you can use formulas.

    Post back with details. :)


  • Okay Doug, sorry this took so long to get back. (posting under different account, whilst the other's under construction :) )

    Paste this into your specific sheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column <> 1 Then Exit Sub
        Application.Run "YesNo"
    End Sub


    Now in a standard module, paste this in there:

    Sub YesNo()
        Dim r As Range, s As Range
        Set r = Range("A2", Range("A65536").End(xlUp))
        For Each s In r
            If s.Value = "Yes" Then
                Range(s.Offset(0, 1), s.Offset(0, 4)).Locked = True
            Else: Range(s.Offset(0, 1), s.Offset(0, 4)).Locked = False
            End If
        Next s
    End Sub



    Works pretty good for me, only locks B:E (of that same row) for each Yes/No.


  • A little snippit of the sample file that is attached looks like this:

    Microsoft Excel - Book3___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    		Yes/NoNameM/FEyesHair
    2
    		YesDougMBlueBrown
    3
    		YesZackMGreenBrown
    Sheet1 

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The cells in col A are Data Validation/List of "Yes,No" only. Code only allows for the text "Yes", so it can be anything else. HTH


  • Sorry for being vague & confusing. This one's a 2-parter

    1)
    I have set up conditional formatting, to format the text to grey for any persons who have chosen not to attend the function. This is done via a drop down menu and choosing "no". This works well and is not a problem. The second part is where I need the help

    2)
    I want to fix it so that once "no" has been chosen in the attendance column, no other values for that individual can be changed such as the "paid" column for example. So in essence I want to lock the row, dependant on the value of one column within that cell. I also need to be able to change the attendance cell back to yes incase anyone changes their mind

    Am I going about this the right way, or is there a better way to solve the problem?

    Just to clarify the individuals are listed in rows, and the values are listed in columns along the top.

    Thanks

    Doug


  • Hey Zack,

    Thanks for the advice. It took a bit of 'tinkering' with to get it to work exactly the way I needed it to, but I have tinkered, and it does.

    Many thanks

    Doug


  • Glad you got it working! :)

    The other was the wife's account. Don't know what happened there. :confused: Before I realized it..


  • Well if you're wanting to lock some cells based on another cell. sure. The problem would come into play if you wanted to lock THAT cell also along with it.

    And what version of excel are you running? Are you wanting to restrict access to locked cells? This is a breeze in xl XP with sheet protection.







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about Locking cell values dynamically , Please add it free.

    Topics: enart.xn--fiqs8sjn5by0n.com | edit

    Cash Loan: Affable Financial Assistance for Emergencies Learn Spoken English Through the Correct Method