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
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)
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.
If you would like me to describe what any (or all) of the other lines do, please ask.
BFN,
fp.
Q-28233498b.xlsm
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
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
Thanks again.
Richard
OK, Richard.
Thanks for letting me know.
Thanks for letting me know.
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
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
ASKER
Any thoughts on the above? I'm stuck!
ASKER
Anyone? Can anyone help? I don't understand why what I did doesn't work?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A number of people helped - very useful stuff! I shall get back to them with my further questions once I have studied further.
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