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?
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.

Roy CoxGroup Finance ManagerCommented:
Can you attach an example workbook. Presumably you are using Data Validation Lists, but it will be easier to see in an example.
0
BlosMusicAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
I'll have a look shortly. I'm at work so it will be during lunch
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup Finance ManagerCommented:
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
0
Roy CoxGroup Finance ManagerCommented:
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
0
BlosMusicAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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
0
BlosMusicAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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

0
BlosMusicAuthor Commented:
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 . . . )
0
Roy CoxGroup Finance ManagerCommented:
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

0
BlosMusicAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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

0
BlosMusicAuthor Commented:
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.
0
BlosMusicAuthor Commented:
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.
0
Roy CoxGroup Finance ManagerCommented:
Blosmusic has posted that my solution works but does not know how to proceed with marking this as solved
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:
Very, very helpful and it solved my problem. Just what I wanted! Thank you, Roy.
0
Roy CoxGroup Finance ManagerCommented:
Glad to help. Post back if you need further help understanding the changes
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.