+ Reply to Thread
Results 1 to 16 of 16

Macro to Lock cells in protected sheet depending on criteria

  1. #1
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile Macro to Lock cells in protected sheet depending on criteria

    Hi Guys,

    I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

    What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

    Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

    What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

    What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

    Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

    Thanks in advance for any help offered.

    PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
    Attached Images Attached Images
    Best Regards.

    Michael
    -----------------------------------
    Windows Vista, Microsoft Office 2007

  2. #2
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317
    Sorry to be a pain but is there a possibility that this can be acheived?

    Thanks.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Your code would need to Unprotect, work then re-protect unless you Protect with UserInterface. See the examples in the VBA section of www.excel-it.com. If you need help attach an example workbook.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    05-06-2007
    Posts
    29
    hi
    I have made an example sheet you can see the macro written in excel sheet
    It will be called as soon as you change anything in sheet. currently if you change C7 value which is <0 then D7 will be unlocked else it will be locked
    you can built your sheet with same line
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile

    Thanks for your help guys I appreciate your input.

    @suhas.nehete

    Thanks for attaching that sample spreadsheet I shall see if I can adapt that to my worksheet it looks like the sort of thing I am after.

    Thanks again guys I shall let you know if this works or not.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    I think your spreadsheet desdign is amking work for you. Why not have one column in which you enter Credit Note or Invoice then you can use AutoFilter or a PivotTable to see the individual totals for each item.

  7. #7
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile

    Hi Guys,

    I tried your code suhas.nehete and it works great but I need it be dynamic to so that it will work in every row that has data.

    @RoyUK

    I hear what you are saying but this spreadsheet is just simply a record to check what credit notes and invoices that I have received the calculations are just simple I don't really need a Pivot Table as it was only ever meant to be a log really.

    I have attached the spreadsheet, maybe if you have some spare time could you look at it for me and let me know if the code provided here can be modified to tend to my needs.

    Basically in a nutshell this is what I need. if Column C in any row become populated then cells I & J in that row become locked, likewise if column I become populated then cells G & H in that same row are locked.

    How can I acheive this and make the macro work automatically so that it locks the cell after data has been entered into it?

    I ammended it as below but my vba skills are minimal.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    The worksheet is Password protected.

  9. #9
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317
    @royUK

    Sorry about that the password is the same one as in the code I provided which is "open".

    Once again sorry for forgetting to include that information in my previous post.

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Try this, I haven't got time to break the password. Post back if it needs amending

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317
    Hi royUK thanks for the code.

    The Password was "open" (minus the quotes)

    Thanks again I shall check the sheet.

    PS: I have uploaded the file this time minus the password.

    EDIT: Just tested the code and it does not appear to work for me, maybe I did it wrong.

    I entered it as a module and then I clicked on the name tab of the worksheet and clicked 'View Code' and entered it that way but it still does not work. I am at a loss.
    Attached Files Attached Files
    Last edited by Zyphon; 12-17-2007 at 10:14 AM.

  12. #12
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317
    Sorry guys, any further info that may help my cause?

    Thanks in advance.

  13. #13
    Registered User
    Join Date
    12-17-2007
    Posts
    9

    This should work, press Enter Key after inputting your data

    Hope this would help you in your problem.
    You just need to press the enter key and not the arrow keys after you enter your data in the table.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Thumbs up

    @jameslou

    Thank you for your help and input with my problem. I did try your sheet and even if I press Enter the code does not work for me and the cells are not locked. Also I cannot check the code because it is password protected.

    Any other suggestions guys?

  15. #15
    Registered User
    Join Date
    12-17-2007
    Posts
    9
    Zyphoon,

    unprotect the sheet first then protect it, uncheck the select locked cells, it would work just fine.

  16. #16
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile

    Quote Originally Posted by jameslou
    Zyphoon,

    unprotect the sheet first then protect it, uncheck the select locked cells, it would work just fine.
    Sorry for the way late response but I forgot about this thread. Yes pressing Enter does do the trick and locks the unwanted cells fine.

    I take it there is no way to make this work when pressing the TAB key?

    Thanks again for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1