+ Reply to Thread
Results 1 to 21 of 21

unlocking 6 cells when another cell = certain text

  1. #1
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    unlocking 6 cells when another cell = certain text

    Hello, I am completely new to VBA code so not even sure if my request is possible, but if it is I would love if someone could show me how.

    In my worksheet, cell H6 will have text saying "locked" or "unlocked" (based on a VLookup in that cell).
    when locked is selected, the sheet stays as is (the sheet is protected with password)
    When unlocked is selected, I need cells G21, G26, L21, L26, Q21 & Q26 to be unlocked

    Thanks in advance for any help you can provide!!
    Chantel
    Last edited by Chantel; 05-20-2016 at 03:14 PM.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    Try this...
    Please Login or Register  to view this content.
    Last edited by gmr4evr1; 05-19-2016 at 02:12 PM. Reason: Edit* Added the Else True line
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    Hi gmr4evr1

    Thank you so much for responding. I added your code and changed the 1111 password in the code to my own password but when I make the selection to unlock, the 6 cells that should become unlocked are still locked. The pop up error says its protected. What am I doing wrong?

    Thanks

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    The end of the code automatically protects the sheet, so anything done to the sheet after the code runs will give the protected message. Is there a particular cell or range of cells that you need to be able to edit, or should I have coded it so that H6 is always unprotected?

  5. #5
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    I do want the sheet to be protected, but when H6 = unlocked I need the 6 cells to be editable, but changing the cells to unlock should allow for that shouldn't it?
    Last edited by Chantel; 05-19-2016 at 03:18 PM. Reason: spelling: show s/b should

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    Yes, changing H6 to unlocked should unlock the 6 cells and allow edits to be made.

    Maybe I'm confused. If the sheet is protected, you wont be able to change H6. you could right click the sheet name tab and select unprotect then enter the password then change H6, but the code would run and preotect the sheet again.
    If H6 gets changed to unlock, the only cells that will be unlocked are the 6 cells you specified, which does not include the H6 cell.
    Last edited by gmr4evr1; 05-19-2016 at 03:30 PM.

  7. #7
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    I'll explain the use of the cells and maybe that will help clarify what I'm after. Cell H6 gets its value from a dropdown list which is in cell D6. Cell D6 is formatted to be unlocked all the time. The dropdown has 4 Quote Types. Two of these types have predefined markups which the 6 cells pull in via Vlookups. When one of these two quote types is selected in cell D6, cell H6 then has a value of locked, meaning the user can not change the markups in any of the 6 cells. The 6 cells are formatted to be locked by default. the last two quote types do not have any markups assigned to them so the user must be able to enter markup values themselves in the 6 cells.

    Does this make more sense?

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    Yes, I believe I get it now.
    I should have thought of this before....I don't think this code will work because it is based off of a cell that has a formula in it. With the code I provided, I think it will read the formula in H6, not the actual text so it might not work.

    Can you attach a sample of the workbook you are using. It will help me try to get the correct code for you.

  9. #9
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    Just had a thought, have you tried to keep H6 unlocked as you did with D6?

  10. #10
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    still no go I have attached an example for you to work with.
    Attached Files Attached Files

  11. #11
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    Um...do you have something in their that asks for a password on a locked cell? I can unlock the sheet with the pw from my code, but there seems to be another password somewhere.

  12. #12
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    sorry thought I removed all the passwords, try "page"

  13. #13
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    Nope, that didn't work. Where do you have the password in the workbook, there is no code.

  14. #14
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    oh on the review tab there is the Allow Users to Edit. In an older version I had some allowed but I thought I had removed them for this example. I guess it didn't save the change. Try this version.

    PS thanks again for all your help!!
    Attached Files Attached Files
    Last edited by Chantel; 05-19-2016 at 05:09 PM. Reason: add PS

  15. #15
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    I'm not sure but I think it's because there is a formula in H6 so the code is reading the formula and not "Locked" or Unlocked".

  16. #16
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    hmm could we make the code read cell D6 instead and have it unlock the 6 cells when either "Change Estimate" or "Non-Contract Estimate" are selected?

  17. #17
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    I have changed the code to use cell D6 instead and it does work. However, I noticed that some of the cells, specifically the ones shaded light gray, are editable with the sheet protected or unprotected.
    Attached is the updated workbook you provided...
    Chantel-unlocking-6-cells-when-another-cell-certain-text-wd-estimating-sheet-example.xlsm

  18. #18
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    Good Morning gmr4evr1. I tested this version out and it does unlock the 6 cells, but it does so for all 4 quote types, not just the two. I'm beginning to think that what I'm after is just not possible. I do really appreciate your time and effort that you spent on this. You got me further than I would have gotten on my own!

  19. #19
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    My bad, I had goofed some things up....here is an updated workbook...
    Chantel-unlocking-6-cells-when-another-cell-certain-text-chantel-unlocking-6-cells-when-another.xlsm

    PS - You're welcome for the help. I've been there and have no problem helping out others.

  20. #20
    Registered User
    Join Date
    05-11-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    11

    Re: unlocking 6 cells when another cell = certain text

    It works!!! Thanks Again!

  21. #21
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: unlocking 6 cells when another cell = certain text

    No problem-o

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Unlocking Cells if condition met
    By mdmandlaywala in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-09-2015, 11:23 AM
  2. Locking or Unlocking validated cells depending on the previous cell
    By biajw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2014, 11:36 AM
  3. Locking and unlocking a range of cells based on a fomula cell value change.
    By tyeinse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-07-2014, 07:35 PM
  4. Locking and unlocking of cells
    By srinivassurapareddi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2012, 02:19 AM
  5. Locking and Unlocking Cells
    By vikas.bhandari in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2010, 10:27 AM
  6. Code not unlocking cells as it should be
    By nhrav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2008, 02:21 PM
  7. conditional unlocking of cells
    By hiryuu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2005, 07:44 AM

Tags for this Thread

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