Hidden dropdown list arrow in Excel
Solved
nemotri
Posted messages
179
Status
Member
-
valentine du 56 -
valentine du 56 -
Hello,
In a column of an Excel spreadsheet, I have a list of cities. I selected this list => right-click => create a list. Thus, in the header cell of my list (which I renamed "cities"), a small arrow appears that allows me to select a city from a pane and only display the row corresponding to the selected city.
My issue is that this arrow only appears when I select the header cell of my list that I renamed "Cities," but I would like this arrow to always be visible (even when the header cell of the list is not selected) so that the user of the spreadsheet understands they can choose a city. How can I do this?
I have a similar issue in another cell: let's take cell B2 for example, I select this cell => Data => Validation... => Allow: list and in source I put "good; average; bad."
Thus, when I select B2, an arrow appears that lets me choose between good, average, or bad. Again, I would like this arrow to always be visible even when the cell is not selected.
Thank you for your help.
Boris.
In a column of an Excel spreadsheet, I have a list of cities. I selected this list => right-click => create a list. Thus, in the header cell of my list (which I renamed "cities"), a small arrow appears that allows me to select a city from a pane and only display the row corresponding to the selected city.
My issue is that this arrow only appears when I select the header cell of my list that I renamed "Cities," but I would like this arrow to always be visible (even when the header cell of the list is not selected) so that the user of the spreadsheet understands they can choose a city. How can I do this?
I have a similar issue in another cell: let's take cell B2 for example, I select this cell => Data => Validation... => Allow: list and in source I put "good; average; bad."
Thus, when I select B2, an arrow appears that lets me choose between good, average, or bad. Again, I would like this arrow to always be visible even when the cell is not selected.
Thank you for your help.
Boris.
10 answers
Hello,
So you shouldn't make a validation list, but create your dropdown list using the control toolbar and click on Dropdown List Box (it's a combobox)
Double click on the combobox, which will open the properties sheet, look in the list
ListFillRange in the box just opposite, enter the source range, for example A1:A20, and if you want the selected value in your list to appear in a cell (linked cell), look in the list Linkedcell and enter, for example, C5
See you
Mike-31
A problem without a solution is a badly posed problem (Einstein)
So you shouldn't make a validation list, but create your dropdown list using the control toolbar and click on Dropdown List Box (it's a combobox)
Double click on the combobox, which will open the properties sheet, look in the list
ListFillRange in the box just opposite, enter the source range, for example A1:A20, and if you want the selected value in your list to appear in a cell (linked cell), look in the list Linkedcell and enter, for example, C5
See you
Mike-31
A problem without a solution is a badly posed problem (Einstein)
Re,
Yes for E6, I cheated a bit, it’s a simple drop-down list and at the location of the drop-down button I created a button declared in the background, which I associated with a very simple code that makes it so that when I click on it, cell E6 is selected, and the drop-down button comes to the foreground, giving an illusion.
However, for K7, I created a drop-down list from the control toolbar, which I sized to fit the cell and right-clicked/Format Control/Property tab and checked Move and Size with Cells so that it stays attached to the cell.
Then right-click on the sheet tab where this combobox (drop-down) is located, View Code, which opens a blank sheet called Properties where I wrote a simple VBA code that you can copy, if your combobox is named ComboBox1, otherwise it will need to be adapted.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.List = [{"Very Good";"Good";"Average";"Poor"}]
End Sub
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Yes for E6, I cheated a bit, it’s a simple drop-down list and at the location of the drop-down button I created a button declared in the background, which I associated with a very simple code that makes it so that when I click on it, cell E6 is selected, and the drop-down button comes to the foreground, giving an illusion.
However, for K7, I created a drop-down list from the control toolbar, which I sized to fit the cell and right-clicked/Format Control/Property tab and checked Move and Size with Cells so that it stays attached to the cell.
Then right-click on the sheet tab where this combobox (drop-down) is located, View Code, which opens a blank sheet called Properties where I wrote a simple VBA code that you can copy, if your combobox is named ComboBox1, otherwise it will need to be adapted.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.List = [{"Very Good";"Good";"Average";"Poor"}]
End Sub
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Ok, thank you for your answers,
the problem was indeed due to the creative mode being activated.
However, how can I format the combobox: how to change the background color and possibly the font of the text and make it bold?
the problem was indeed due to the creative mode being activated.
However, how can I format the combobox: how to change the background color and possibly the font of the text and make it bold?
Re,
you open the Visual Basic toolbar (View/Toolbars/Visual Basic Toolbar)
Click on Design mode (the second to last button on the right represented by a square and a ruler)
Double click on the combobox to modify (which will open the properties sheet)
In the list, look for BackColor, and click on it to access the color palette
For bold fonts etc., do the same on Font
See you +
Mike-31
A problem without a solution is a poorly stated problem (Einstein)
you open the Visual Basic toolbar (View/Toolbars/Visual Basic Toolbar)
Click on Design mode (the second to last button on the right represented by a square and a ruler)
Double click on the combobox to modify (which will open the properties sheet)
In the list, look for BackColor, and click on it to access the color palette
For bold fonts etc., do the same on Font
See you +
Mike-31
A problem without a solution is a poorly stated problem (Einstein)
Hello,
Don't forget to set your status to resolved at the top of the discussion.
Thank you
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Don't forget to set your status to resolved at the top of the discussion.
Thank you
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
It's a bit late... but the perfect and easy solution:
http://boisgontierjacques.free.fr/pages_site/donneesvalidation.htm#Fleche
http://boisgontierjacques.free.fr/pages_site/donneesvalidation.htm#Fleche
Re,
As I told you in the other discussion, it's unnecessary to open multiple discussions on the same topic.
Download the file with several examples of dropdown lists; I think you are looking for the one expressed in K7.
https://www.cjoint.com/?jroJI1GJJ3
See you!
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
As I told you in the other discussion, it's unnecessary to open multiple discussions on the same topic.
Download the file with several examples of dropdown lists; I think you are looking for the one expressed in K7.
https://www.cjoint.com/?jroJI1GJJ3
See you!
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Thank you, Mike, for this response,
I'm sorry for creating a second topic, but as in my first topic, I felt like I was asking two different things, so I thought it would be best to create a second topic.
For the file you attached, I indeed need to reproduce either the list in E6 with the small macro or the list in K7 (the ideal could be).
For the one in E6, I think I understood; it's what I was doing, except that you invented a macro to always select E6, right?
For the one in K7, it's a combobox, apparently, but I don't know how to use that function.
I probably need to insert a dropdown list from the "form" toolbar. Then I double-click on the combobox, and VBA opens with the following text:
"Private Sub ComboBox1_Change()
End Sub"
But what procedure should I write here?
Thanks in advance.
I'm sorry for creating a second topic, but as in my first topic, I felt like I was asking two different things, so I thought it would be best to create a second topic.
For the file you attached, I indeed need to reproduce either the list in E6 with the small macro or the list in K7 (the ideal could be).
For the one in E6, I think I understood; it's what I was doing, except that you invented a macro to always select E6, right?
For the one in K7, it's a combobox, apparently, but I don't know how to use that function.
I probably need to insert a dropdown list from the "form" toolbar. Then I double-click on the combobox, and VBA opens with the following text:
"Private Sub ComboBox1_Change()
End Sub"
But what procedure should I write here?
Thanks in advance.
Re,
You must be missing something or the code is out of place,
If your file does not contain any confidential notes you can put it on the forum with this link
https://www.cjoint.com/
or in a private message, for that click on my username Mike-31 and send a private message, you paste the link generated by
https://www.cjoint.com/
If that is not possible give me the number of your comboboxes and the code you entered in VBA
A+
Mike-31
A problem with no solution is a problem poorly stated (Einstein)
You must be missing something or the code is out of place,
If your file does not contain any confidential notes you can put it on the forum with this link
https://www.cjoint.com/
or in a private message, for that click on my username Mike-31 and send a private message, you paste the link generated by
https://www.cjoint.com/
If that is not possible give me the number of your comboboxes and the code you entered in VBA
A+
Mike-31
A problem with no solution is a problem poorly stated (Einstein)
But that's no longer what I want to do. What I would like is for a cell, let's say B2, to have an arrow that opens a dropdown from which I can choose, for example, between: good, average, and bad, and when I click on it, it displays in B2. However, these sources are not marked anywhere else in my spreadsheet and I therefore don't have a source range.
That's for the second case I mentioned.
For the first case, I'm sure I did what I needed because I was inspired by another file that a friend sent me and I achieve the same result, the only difference is that in his file the arrow is always present even when the cell is not selected, and in my case, I have to select the cell for the arrow to appear :-(