• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto expansion of Dropdown list

R

Raja Kamarthi

New Member
Hello Friends,


I have a sheet with 2 dropdown lists updated in the cells E10 & G10. E10 has the list of the managers and G10 has the list of engineers associated with the respective manager.


Is there are an option where the dropdown list of cell G10 expands (same as you click on the pointer of the dropbox) automatically after I select the manager name in cell E10.


Can this be done using VBA?


What I am looking here is, once I select the manager name in cell E10, then the next action of excel should be the dropdown list in cell G10 should expand.


Am sorry if my words sound weird and confusing.Please refer to the sample file for more clarity


https://www.dropbox.com/s/0cjzlduwn8x54uz/Sample%20File.xlsx?m


Regards,

Raja
 
Good day Raja Kamarthi


I think you are talking about Dependent drop downs, this link will take to the Contextures site where all will be explained. You do not need VBA


http://www.contextures.com
 
Hi Raja ,


Can you explain why you want an expansion of the dropdown box in G10 ? After all , when you click on G10 , you do get the correct dropdown.


Narayan
 
Hi Raja ,


What you can do is insert a Label control just next to the second dropdown , and use the following procedure :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("E10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Label1.Caption = ""
For Each cell In Range(Replace(Target, " ", ""))
If Label1.Caption = "" Then
Label1.Caption = cell
Else
Label1.Caption = Label1.Caption & Chr(10) & cell
End If
Next
Application.EnableEvents = True
End Sub
[/pre]
Narayan
 
@bobhc: Thanks for your comment, would definetly browse the link shared by you.


@Narayan: As you rightly said, I do get the correct dropdown but I want the dropdown list to open up on its own without clicking on the box appearing at the extreme right of the cell.


I doubt my word "EXPANSION" of dropdown was not the right one to use in this context.

The list of the engineers remains same and there are no additions to that.


Let me try to explain in detail and what inspired me to replicate the same into my report.


Please access this link: http://www.makemytrip.com/

On the homepage, select any city name from "Leaving from" drop down and next select any city name from "Going to" drop down.

The next thing to happen is "THE CALENDAR OPENS UP" automatically without clicking on the "CALENDAR ICON".


Now this is something which would make the end user's life bit easy and watching this spraked a thought of having something same on these lines in my report.


When the end user of my report selects the manager's name, the dropdown list of cell G 10 should automatically open up so that the user can just select the engineer name.


The only reason me digging into this is to enhance the sofistication of my report amd also amaze the end users


Raja
 
@Narayan:


Just noticed your last post. Let me check if your code does the trick for me


Thank you so much for your time and support


Raja
 
Hi Raja ,


Use this instead :

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("E10")) Is Nothing Then Exit Sub
Range("G10").Select
Application.SendKeys ("%{DOWN}")
End Sub
[/pre]
Courtesy : http://stackoverflow.com/questions/6525462/on-focus-after-tabbing-of-excel-drop-down-automatically-show-list-for-selection


Narayan
 
Hello Narayan:


Tried the above code but unfortunately didnt get the desired result.


Could you please let me know if I'm missing out on something?


Thanks,

Raja
 
@Raja


Hi


Which Code is given by Narayan, please copy and paste the code in your Worksheet where the Level/ComboBox is stored and check


the code specified by Narayan

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("E10")) Is Nothing Then Exit Sub
Range("G10").Select
Application.SendKeys ("%{DOWN}")
End Sub
[/pre]

Thanks


SP
 
Hi Raja ,


I cannot say. I have tried it out and when I select an option from the first dropdown in E10 , the dependent dropdown in G10 immediately opens.


Do you want me to upload your file with the code in it ?


Narayan
 
Hi all.


Along these same lines, I have data validation set up for all cells in column B. How can I go about auto-expanding the cell I highlight?

I want them to auto expand when I click on a cell in B and have the adjacent cell in A = not blank.

Tried changing the script above to:


Private Sub Worksheet_Change1(ByVal Target As Range)

If Application.Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

Range("B:B").Select

Application.SendKeys ("%{DOWN}")

End Sub


But this does nothing. Also, note I have changed Worksheet_Change to Worksheet_Change1 because of duplicate labels.
 
Hi Marques ,


First , regarding the change in the macro name - the Worksheet_Change macro runs automatically when ever a worksheet cell is changed manually by the user , either through data entry , or pressing F2 and then the ENTER key , or pressing the DEL key , or by copy + paste operations.


The moment you change the name to Worksheet_Change1 , the macro is not going to run automatically.


If you say that you already have a Worksheet_Change procedure in your workbook , and you want the drop-down to drop when ever you select a cell in column B , then you can probably use the Worksheet_SelectionChange event procedure to do what you want ; of course the code will have to be modified , since you want the procedure to work in every cell in column B.


Narayan
 
Thanks for taking the time to reply.

You can find a copy of the file in question at http://db.tt/vSV3Z6qz


As you can see I have a Private Sub Worksheet_Change and a Private Sub Worksheet_SelectionChange


The first, unhides the next row when the A column of current last row is filled in, and then fills in the formulas. I'm guessing it's somewhere here that I have to change things to get the dropdown in sheet 'Dados' to work.


The second allows me to jump over columns C to E and hopefully AC. These columns are auto populated when I fill in data in predeceasing cells and hence I have no reason to go through them. That's why I want to jump over.


So right now my 2 main goals remaining for this sheet is to have the dropdowns in column B of 'Dados' to show when focused, and then to have the focus jump from last cell in the current working row, over AC of the same row, and go land focus on column A of next blank row.


Tall order for me, I know. But I'm learning. What has been done so far I have managed by pulling together bits and pieces of info. Took a metric nut-ton of time too. Right now my main focus is getting the drop down to work. After I'll focus on jumping over AC down to next A.


Any help appreciated.
 
Hi Marques ,


Can you check your file here ?


https://www.dropbox.com/s/5pun1az22h4ubql/20120510_Recolha%20Info%20CRD_v01.xlsm


I think the Worksheet_SelectionChange procedure works on column AC , by moving to the next row in column A. Is this one of the points you wanted ?


Regarding the drop-downs in column B , give me some time.


Narayan
 
Yeah. Wow. That takes care of the return at end of row. Perfect. As for the drop downs... Sure! No worries mate. After all, you aren't even obligated to spend your precious time trying to help obnoxious pricks like me :p


I am grateful for your help so far.
 
You are a mad genius! Moving it to Selection instead of change. Brilliant idea! That fixed it. That fixed all of it! Props to you, sir! You have won the Internets for today!
 
Good day nmgmarques


Nice to have you on the forum...........but who have to do something about the tie....:)


Quote"After all, you aren't even obligated to spend your precious time trying to help obnoxious pricks like me :p"....we do not have these on the forum so you can not be :)


All though you question is similar it still has its differences and would attract a lot more interest if posted a new, those on the forum who have read the original post may not re-read as they think there is nothing new.


New question = new post with a good title will always attract the most response.
 
I'll keep that in mind. As for the tie and the avatar, I don't know where that came from. I didn't even choose an avatar when registering and I can't find a way to change it either.
 
Good day nmgmarques


This site is the one recommended to find and upload an Avatar of your choice


https://en.gravatar.com/
 
You must log in or register to reply here.
Back
Top