You open the form with the double-click (or some other way, such as a hot-key like Ctrl+some key, or a button, or a right-click) but, the form stays open until you dismiss it. might I suggest that you use instead a non-modal form. If your intent is to fill a column with validated data, in a way that allows auto-complete, and avoids the need to continually double click to display the combo-box. (I have sucessfully subclassed a minimized Excel window to trap and discard any messages that would make the window visible or give it focus.) At least if you use VBA for doing the subclassing. There are SO MANY events that Excel already traps and responds to that if you subclass a visible window in Excel, you will end up freezing the application. But, there is a problem doing this with Excel. Note you will also have to add a bind-value'PropertyNameHere' as well. bind only databinds during the onchange event which requires losing focus on the input whereas bind-value:event'oninput' databinds on every keystroke. This is called Subclassing or Hooking (depending on where in the Windows operating system you choose to insert your code). tldr Use bind-value:event'oninput' instead of bind in order to get real feedback as you type. So, one could trap this event at the operating system level. This event also exists, of course for the Windows operating system. But, you want to avoid using an ActiveX object because it is not quite as seamless as entering directly into text. This event DOES exist for ActiveX objects (such as a TextBox) and for Forms. Not in the worksheet events (which are easily coded), not in the workbook events (which are easily coded), not in the application events (which require using a Class Module, therefore are more difficult to code). In other words, you want to trap each time the user FINISHES a keystroke.Īs you point out, there is no such event in Excel. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Hide combo box and move to next cell on Enter and Tab Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) could be replaced with something like Worksheet_Cell_OnEnter ? Of course, that event does not exist in Excel. I have downloaded a sample file that requires the doubleclick and am including the VB code for your review. Is anyone able to show me an example using my sample data which does not reqiure doing anything but simply typing data into cell A1 to achieve the desired results? However, those examples require that cell A1 be double clicked on with the mouse in order to display (pop up) the combo box. I have seen examples on the internet that employ the use of a combo box and VB code. Lets then say that if I continue to type "JE", the result in cell A1 would be "Jerry". In this case "J" would result in "Jack" in cell A1. I have searched previous messages but do not see exactly what I need:Ĭell A1 is where I will type data which looks up data from a list (A10:A20) described below.Īs soon I start to type letters into Cell A1, (for instance) "J" the cell would automatically fill the cell with the value that most closely matches the item in the list located at A10:A20.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |