Help with macro for locking drop-down to "No" if certain criteria is met

[luajambeiro]

Hi,

I'm using a template that has several worksheets and if 2 criteria are met I'd like to lock the drop-down to "No" so that user cannot select "Yes"

The dropdown is in range D4:D13

So if:
1 - The domain in "Team Users" worksheet cell H2 (always the same cell) is "DDC" or "GHY" (this is a cell with a formula that will bring the domain) AND
2 - The value in "Template" worksheet range W2:W11 = "TRUE" (these are cells with formulas that will be either True or false)

I'd like the drop-down in range D4:D13 to be locked as "No"

(D4 is equivalent to W2 in the Template worksheet, D5 -> W3 and so on. So if cell H2 = either "DDC" or "GHY" and value in W2 = "TRUE" then drop-down should be locked as "No" in D4. If it's W3 then drop-down should be locked in D5 and so on).

I'm not sure if this is possible but thanks in advance for any help!

[dflak]

It seems very possible and might not even require VBA - I think a named dynamic range might work.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[luajambeiro]

Thanks for the response @dflak.

I had to remove a lot of data due to confidentiality but I added the example sheets to show how I'd like it to look. I just wasn't able to actually lock the cell with No, but hopefully I explained well enough so it can be understood.

Let me know if you have any questions. The file is attached.

[dflak]

I made two lists on Sheet2. One for when Column A is blank and the other for when it isn't blank. The validation criteria itself handles the logic. The source is =IF(ISBLANK($A5),List_1,List_2)

[luajambeiro]

Hi @Dflak. I'm confused about the other criteria from the other worksheets as the "Yes/No" should be conditioned to them and not whether A has data or not. Maybe I wasn't clear in the explanation.

If data in "Template" worksheet column W = "TRUE" and data in "Team Users" worksheet = "DDC" or "GHY" then column D of Sheet 1 should only show the No option
If data in "Template" worksheet column W = "FALSE" and/or data in "Team Users" worksheet = anything else other than "DDC" or "GHY" then column D of Sheet 1 should show the Yes/No option

Thank you for the help!

[dflak]

The template sheet has three lines that indicate False. The Team User sheet has one entry. Sheet 1 has no data on it all. The drop-down in column A does not exist.

How do the Template and Team User sheets relate to Sheet 1. Specifically What cells on these sheets should Cell D4 look at and Cell D5, etc.?

[luajambeiro]

Sorry for confusing you!

I updated the attachment and added some comments in the Sheet 1 - Example tab. Please refer to the comments in red in that tab and to the example sheets.

But to answer your comments:

The template sheet has three lines that indicate False - This should have 10 lines. Sorry, I just added the 3 for the example but have updated to show the 10 lines.

The Team User sheet has one entry. - This will always only have 1 row and the only entry that matters in the one in column H - Domain, which is auto-populated when the user opens the file based on a connection

Sheet 1 has no data on it all - The template is usually like this. Blank. The user goes in and fills out the info. The example sheets have some rows filled out like they would..

The drop-down in column A does not exist - It doesn't need to be a drop-down. We can assume they can type in anything they want in those cells. They do select from a drop-down but I had to remove the data and that doesn't impact anything whether they type or select from the drop-down for this purpose .

How do the Template and Team User sheets relate to Sheet 1. Specifically What cells on these sheets should Cell D4 look at and Cell D5, etc.? So cell D4 will look at cell W2 and cell H2, cell D5 will look at cell W3 and cell H2, Cel D6 will look at cell W4 and cell H2, etc. etc.... up to cell D13 that will look at cell W11 and cell H2..

I hope this and what I wrote in the file helps clarify.

[luajambeiro]

Hi.. just checking if any luck on solving this..

Thank you!

[dflak]

Not on Monday morning when my reports are all due.

[luajambeiro]

No worries. I appreciate the help!

[dflak]

OK, I think I have it figured out.

The data validation is off a named dynamic range called List_Dropdown which is defined as: =IF(AND(Template!$W16=TRUE,OR('Team Users'!$H$2="DDC",'Team Users'!$H$2="GHY"))=TRUE,List_2,List_1)

This name points to List_2 (No only) if the conditions are met or List_1 (Yes,No) if the conditions are not met.

[luajambeiro]

Thank you Dflak!

It worked. I know I'm "over asking" and I actually don't think this is possible, but just to check.. Is there a way I can adjust the formula to already populate the cell with a "No", instead of having to choose from the drop-down (even though the drop-down only has the no option)? This is because I'm trying to make the template easier to fill out, which means if they meet that criteria they don't even need to go through the drop-down bc the No will already be populated on there.

If it's not possible, that's ok!

Thank you!!

[dflak]

It takes a little bit of code on the sheet module.

Please Login or Register  to view this content.

[luajambeiro]

Thank you so much for all your help Dflak! It works great! Thanks for taking the time! =)

[luajambeiro]

Hi Dflak,

Not sure if you're still checking this, but just have a quick question..

Is it possible for me to add something in the code to make it only work if there is a value in any cell in the range (A4:A13)? In other words, if the cells in range A4-A13 are blank the macro doesn't work. Because right now whenever I click on any cell in column D it auto-populates with the No, but if possible I want it to only auto-populate when there's data in column A (A4:A13).

Thanks in advance!