Link to home
Start Free TrialLog in
Avatar of BlosMusic
BlosMusic

asked on

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
Avatar of [ fanpages ]
[ fanpages ]

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
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)
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
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
OK, Richard.

Thanks for letting me know.
Avatar of BlosMusic

ASKER

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
Any thoughts on the above? I'm stuck!
Anyone? Can anyone help? I don't understand why what I did doesn't work?
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A number of people helped - very useful stuff! I shall get back to them with my further questions once I have studied further.