I created my own dropdown..

[garyj]

Hello..

I created my own Excel dropdown and thought I'd share it here. Feedback is good.

The data validation has very small text that cannot be edited. Same is true for the user form dropdown. I believe the Active X dropdown font size can be modified, but Active X comes with another set of problems. So, after some thought, I figured I would see how hard it could be to make my own. Perhaps it's been done before, but my research did not find anything. Here it is.

I began by making a textbox on the worksheet. All this can be done by code, but I did it with the Excel menu commands.
- Menu: Insert / Text Box (1st one in list); click on spreadsheet, type random text "Boo", size as desired, select font and size..
- Copy and paste 4 more text boxes.
- While a text box still selected, Menu: Shape Format / Selection Pane; I used this to rename them TB1 thru TB5. Can also be used to select/deselect or hide/unhide.
- I then manually stacked them one below the other, and aligned them, and grouped them, naming the group "DpDn1", and giving it a border.
- I then did a screen copy of a drop down button, and pasted it to the sheet, placing it on top of DpDn1 at top right corner.
- It looks like this.. and obviously the random text has nothing to do with the Method of Payment haha.

31713

Coding is as follows:
Two macros created in a module: the first is attached to the dropdown arrow picture, so when it is clicked the dropdown opens. The second is attached to each of the 5 textboxes, so that no matter which is selected the SelectTB runs. MyStr = the text in the box selected, and of course could be manipulated to do whatever from there.
Follows the code inserted into Sheet4 code under selection change as the first line to close the dropdown in case nothing is selected after the dropdown pic is selected.



Sub SelectDrpDwn()
Sheet4.shapes("DpDn1").Visible = msoTrue
End Sub

Sub SelectTB()
Dim myStr As String
myStr = Application.Caller
Sheet4.shapes("DpDn1").Visible = msoFalse
Application.ScreenUpdating = True
MsgBox Sheet4.shapes(myStr).TextFrame.Characters.Text
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet4.shapes("DpDn1").Visible = msoFalse


I was not able to find a way to change background of textbox on mouseover, but I don't know how necessary that is.


That's it folks. A dropdown made without lots of code, and it works.

Gary

[Paul_Hossler]

Interesting

Some of the macro got cut off so could you attach a sample workbook?

[garyj]

Interesting

Some of the macro got cut off so could you attach a sample workbook?

I am going to try to attach a sample workbook. I copied and pasted the page to a new one.
31717

By the way, nothing is cut off there. The next line could as well be "End Sub". I left it out because anything else after would be not applicable to the dropdown.
The reason for that last bit is so that if a person clicks off the dropdown arrow onto the spreadsheet, then change selection will hide the dropdown menu.

By the way, if you are interested, I am almost finished making the dropdowns dynamic through code. When I use data validation I can put the list on a Hidden sheet, reference it with a dynamic command in the next column, name the first line as "MyList", and then reference it in the data validation as Hidden!MyList#. That way if an item is added to the list, the dropdown shows it automatically. With my dropdown that won't happen because each item is hard coded into each textbox, and the number of textboxes is limited to what is created. So my code will use provide variables that reference the activesheet, send the routine to a sub that will check the count of the list, and loop to create that many textboxes, placing the applicable text into each one, etc. The code will name the group and give a suffix - a number that signifies the count of the list. So the first command would be to check if that suffix is equal to the list.count, and if so then no reason to recreate the dropdown.

Gary

[Aussiebear]

By the way, nothing is cut off there. The next line could as well be "End Sub". I left it out because anything else after would be not applicable to the dropdown.


How was anyone other than yourself meant to know what the next line was meant to be?

[garyj]

How was anyone other than yourself meant to know what the next line was meant to be?

My apologies. I meant no sarcasm in my response, nor in this one. I recognize that one of my failings in communication is assumptions made and I am always working on that. However, to answer the question there were two things I had put down to give the answer to that.
* What I wrote in the original post referencing that particular line: "Follows the code inserted into Sheet4 code under selection change as the first line to close the dropdown in case nothing is selected after the dropdown pic is selected."
* An assumption that a coder would recognize that the code: Sheet4.shapes("DpDn1").Visible = msoFalse always hides the shape on the selection of any other cell on the sheet.

Thanks Paul for the reminder not assume and to be more clear. I am also learning to put comments into my code so that I can follow it later. I never studied this in school, just on my own. So it sometimes shows.

Gary

[garyj]

Since this post is not really a question I am going to mark this thread solved. I am one that tends to look for tricks like this from time to time. I tend to stay away from ActiveX OLE objects just because of what others have said about them causing issues. The form controls are also limited when it comes to font sizing, etc. This is why I decided to try using a list of textbox shapes to create my own dropdown.

By the way, I have now made it so the dropdown can be created on the click of the dropdown arrow pic. How does this work?

I store in a "Hidden" sheet the following in a table: CellName (where dropdown will sit), SheetCodeName, TriggerName, TextBoxNameBase, TextBoxGroupName, DynamicListName, LengthLastList. A line above the Table allows for entry of the SheetCodeName and the TriggerName, from which a presest Filter formula gets the remaining details. The programmer does have to set all these names into the table and make and set the trigger and its name, and create the dynamic list in the hidden sheet. Then when the user clicks the dropdown the VBA will use the sheet and trigger name to create the textboxes and fill them with the dynamic list text. The time taken is unnoticeable - Click - Dropdown comes down. User clicks to select an item and VBA puts that value in the cell and hides the textbox group.

I also made it so that when the user clicks a trigger, the VBA will check three ways... 1) If the textbox group exists as a hidden element, 2) If the size of the hidden textbox group is the same as the current dynamic list, and 3) If the items on that list are the same as those in the hidden group. I had a reason for each test, and if any could be deleted it would be the second. Anyway, if all three pass, then the hidden dropdown is dropped. If it doesn't the hidden dropdown is deleted and a new one created.

Since my code works through the Table now, I have changed it from the file I previously shared. It now uses string variables to store the values in the Table so that regardless what page a user is on, it will still work.

If anyone wants to look at this, I am willing to share. Leave me a message in my Inbox.

By the way, I have also created my own date picker... was so tired of trying to locate one that still works. Mine works on textboxes in a user form. It is fast.
I am also now realizing that a the same kind of system can be used to create a Menu. I've made one for a sheet that uses the hidden group textbox idea. Currently it is resident on one sheet only, so a link would be required to take user to the sheet to select the next choice on the menu. The alternate would be to create code that would make a menu according to hidden lists on whatever page the user is on.

Gary