Link to home
Start Free TrialLog in
Avatar of BlosMusic
BlosMusic

asked on

Drop-down box to only show relevant items

I have a (well-functioning) drop-down box that gives me various choices. Once I've chosen the item I want from that drop-down box, I have managed to get displayed, in another drop-down box, the relevant sub-objects that are relevant to the choice I made in the first drop-down box. I did that by means of an HLOOKUP function elsewhere that selects a list by looking up the data from an array. That all works fine.
However, when I select my main choice in the first drop-down box, I still see, in the second drop-down box, the list from my previous choice. Only when I click the down-arrow for the second drop-down box do I then get the list within that drop-down box that I want, the list that is relevant to my main drop-down box. The problem will be that unless the user of my spreadsheet thinks to actually click the down-arrow in the second drop-down list, she/he may well think that the choices displayed in the second drop-down box are relevant, which they will not be if the main choice has changed.
Is there a way to make my second (dependent) drop-down box automatically display the correct choices based on the selection in the first (main) drop-down box?
I have come across this sort of issue before, and someone solved the problem by means of VBA, but (a) I'm not adept at VBA, and (b) is there a way to do what I want without using VBA? If there isn't, is there someone who can simply explain to me how to code for this?
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you attach an example workbook. Presumably you are using Data Validation Lists, but it will be easier to see in an example.
Avatar of BlosMusic
BlosMusic

ASKER

Hi Roy,
Example (my original spreadsheet is very big, and contains sensitive info., but the attached is exactly my problem). Can you take a look?
Thanks,
Regards,
Richard
Example-of-drop-down-box-problem---15062
I'll have a look shortly. I'm at work so it will be during lunch
I've actually had a quick look. Your lists are working as expected, but K24 keeps the last entry. I would suggest some simple code to clear K24 when K21 changes. That will mean the user needs to check K24.

let me know if this helps.
Example-of-drop-down-box-problem---15062
I've just noticed that the file is showing as a .zip

I suspect it may be the length of the file name.
EE-Drop-Down.xlsm
Regarding your first posting - my problem will be that I just don't know the coding necessary; for the second posting, I don't really understand what you are saying: are you suggesting that the length of the file name is affecting the drop-down boxes? (I think probably not!).
Regards,
Richard
Your file was changin to a different file extension, I don't know why.

I've added some code to clear K24 in my example in the last post I made.

Open the file, enable macros when prompted. Then change the K21 entry using the drop down. K24 will automatically clear and the use will have to then click on the drop down

Here's a video showing how to enable macros

Enable macros Video
Thanks. Sorry to be a burden, but as I said, I can't send the whole spreadsheet to you, so I made another one to indicate the problem, and that was the one I sent to you. Can you give me some guidance on how to adapt your code to work in my proper spreadsheet. The equivalent of my cell K21 in the real spreadsheet is cell D52, and the equivalent of cell K24 in the real one is F52 (see attached screenshot).
I am a willing learner, but busy (aren't we all?), and a quick adaptation of your code to get my real S/Sht working would be magic!
Screenshot-of-actual-dropdown-boxes.jpg
No problem

Add Excel VBA Code to a Worksheet Module

This code is Event code, and will run automatically when a specific action occurs in  the worksheet For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code to open the Visual Basic Editor.

You will see

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$K$21" Then Target.Offset(3).ClearContents
End Sub

Open in new window


Copy this line below and replace the existing code with it.

If Target.Address = "$D$52" Then Target.Offset(, 2).ClearContents

Open in new window

I tried this, but there is already some code in there, in my Spreadsheet, that someone else put in there previously, which does something similar to what I am trying to do; and I can't delete that stuff!
If I sent the whole spreadsheet to you - just to see what I actually have - would that be possible? Perhaps through your private email address?
I don't want you to do my work for me as such, and I do understand what you are saying in your code, but I need this to work fairly soon, and I can't read a whole book on VBA just to do this!!!
Alternatively - how about this? The existing code on my real spreadsheet - on the worksheet concerned - is:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 21 And Target.Column = 8 Then
    If Cells(21, 8) = "No" Then
        Cells(34, 4) = 0
    End If
End If
End Sub

This code was to suppress a whole lot of cells depending on the selection in another drop-down box (a different part of the worksheet, unconnected with my current problem), and it works nicely; but how do I add your code to this?

I know, I know - I will have to study VBA (in the longer term . . . )
See if this works for you

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$H$21" And Target.Value = "No" Then Cells(34, 4) = 0
If Target.Address = "$D$52" Then Target.Offset(, 2).ClearContents
End Sub

Open in new window

No, it doesn't seem to work. I still have the secondary drop-down box for the previous selection until I click the down-arrow, and also I get a   'Run-time error '13'. Type mismatch'   message
The code that I wrote will work. However, your cell is merged so the Offset needs adjusting

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$H$21" And Target.Value = "No" Then Cells(34, 4).Value = 0
If Target.Address = "$D$52" Then Target.Offset(, 1).ClearContents

End Sub

Open in new window

This works, thanks! I am indebted to you. Now I have to work out what you did, and WHY it works!
BTW, how do I tell EE that my question is answered? I cannot see any button that tells me what to do.
I've requested that this question be closed as follows:

Accepted answer: 0 points for BlosMusic's comment #a40852673

for the following reason:

I meant only to select the Expert's solution, which was great.
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Very, very helpful and it solved my problem. Just what I wanted! Thank you, Roy.
Glad to help. Post back if you need further help understanding the changes