Conditional drop-down box

I want to suppress a drop-down box under the condition that another drop-down box says "no". So, in the attached file, if the drop-down choice in E6 is "NO", then I want the  drop-down box in E7 to be suppressed entirely. is this possible? If so, how? Or is there a better approach altogether? See attached fileLatest-new-effort---130905.xlsx
BlosMusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I was not sure what you meant by "suppressed entirely", but please see if the attached workbook meets your requirements.

The display of "(n/a)" in place of the drop-down list can be removed, if required.

BFN,

fp.
Q-28233498.xlsm
0
BartokblosCommented:
Thanks! How did you do that (I can't see what you have written to do what you did), and yes, how could I remove the 'n/a'?
Thanks again.
Richard (my real name)
0
[ fanpages ]IT Services ConsultantCommented:
Hi Richard,

I added some Visual Basic for Applications [VBA] code within the code module for the [Sheet1] worksheet within the Worksheet_Change() event subroutine.  This code is run every time a change is made to any cell in that worksheet.

I have attached an updated workbook that includes the code below.  This revision simply clears the contents of cell [E7], rather than changing it to the explicit value of "(n/a)".  I have left the original line in the code so you can see where it used to be, but I have placed an apostrophe (') at the beginning of the line to change that line to a comment rather than an executable line of code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

  On Error Resume Next
  
  If Target.Address = [E6].Address Then
     If [E6] = "No" Then
        [E7].Validation.Delete
'       [E7].Value = "(n/a)"
        [E7].ClearContents
     Else
        [E7].Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Volume"
        [E7].Validation.IgnoreBlank = True
        [E7].Validation.ShowInput = True
        [E7].Validation.ShowError = True
        [E7].Value = Worksheets("Sheet2").Range("Volume").Cells(1&)
     End If ' If [E6] = "No" Then
  End If ' If Target.Address = [E6].Address Then
  
End Sub

Open in new window


If you would like me to describe what any (or all) of the other lines do, please ask.

BFN,

fp.
Q-28233498b.xlsm
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

BartokblosCommented:
Thanks, fp  As I am not at all clued up on VBA code I going to do some quick learning . . . . . and then I shall understand it better, and get back to you with any questions.
Thanks again.
Richard
0
[ fanpages ]IT Services ConsultantCommented:
OK, Richard.

Thanks for letting me know.
0
BlosMusicAuthor Commented:
fp,
I hope you don't think I am just using you to learn VBA coding, because I'm not! But I have gone back to basics, because I've never used VBA before (you have set me on a new path!) and I have downloaded Excel VBA Programming for Dummies. I tried the first example "Recording a Macro", in which you get to try a macro which uses Ctrl+Shift+N to simply put your name and the date in any box at the click of a (Ctrl+Shift+N)! It worked the first time, and when I studied what I did, and tried it again - it wouldn't work. I have persevered for two days (off and on), and am going mad. What on earth is happening? I am following the "Dummies" book diligently, but it just doesn't work any more! Once I get the hang of VBA, enough of a hang, I will be able to do my simple stuff and not bother you too much any more - or at least ask intelligent questions! - but at the moment I am going mad with frustration. What am I doing wrong? Please see attached file, in which i did EVERYTHING exactly as the book said!!!
Date-and-Time.xlsm
0
BlosMusicAuthor Commented:
Any thoughts on the above? I'm stuck!
0
BlosMusicAuthor Commented:
Anyone? Can anyone help? I don't understand why what I did doesn't work?
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

If you close this thread, accepting a suitable solution comment, & start another I will be happy to assist further.

Once you have done this, please post a link within your closing comment here so I don't miss the new question.

Thank you.

BFN,

fp.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlosMusicAuthor Commented:
A number of people helped - very useful stuff! I shall get back to them with my further questions once I have studied further.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.