Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VBA - DblClick Object Activate

Posted on 2011-10-27
13
Medium Priority
?
587 Views
Last Modified: 2012-05-12
Hello.  I have an Excel file that contains an activex text box and list box.  When users double click the text box (MyTextBox) the list box (GenericListBox) opens and the first row in the list box is selected.

The problem I'm having is I can't figure out how to "Activate" the list box and make it stay activated.  You'll notice the line "GenericListBox.Activate", but when the code runs the focus immediately goes back to the text box.  

Anybody have any ideas how to keep the list box activated?  Below is the code, and attached is a simple example in the spreadsheet.

Thank you.
Private Sub MyTextBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'When user double clicks on this text box the generic listbox fills and displays.
        GenericListBox.ListFillRange = "luInvoiceNumber"
        GenericListBox.Visible = True
        GenericListBox.Activate
        GenericListBox.Selected(Counter) = True
        Set LookupCellRange = Worksheets("Sheet1").Range("P3")
        Set SourceTextBox = ActiveSheet.OLEObjects("MyTextBox") 
End Sub

Open in new window

Book2.xlsm
0
Comment
Question by:jobprojn
  • 8
  • 4
13 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37043000
It has to do with your sequencing of actions.

For example, I can get it to work with:

Private Sub MyTextBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'When user double clicks on this text box the generic listbox fills and displays.

        Application.Wait Now + TimeValue("00:00:01")
       
        GenericListBox.ListFillRange = "luInvoiceNumber"
        GenericListBox.Visible = True
        GenericListBox.Activate
        GenericListBox.Selected(Counter) = True
        Set LookupCellRange = Worksheets("Sheet1").Range("P3")
        Set SourceTextBox = ActiveSheet.OLEObjects("MyTextBox")
       
End Sub

I would have thought DoEvents would have done the trick, lol.

Still looking for something more elegant, but it probably has to do with the hierarchy of events and their timing, in spite of the fact that putting a DoEvents instead of the Wait didn't work for me.  Unless I've put it in the wrong place...

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043008
With the soluion, above - you can use arrowkeys on the listbox, but when you start typing - you're back in the text box.  Where do you want to be when you hit ESC to get out of the listbox?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043025
I believe the root cause is that while you're manipulating the ListBox, the Double-click event from the textbox is not done, so code finally comes back to that routine and this Excel puts you back in that state.  

Given that explanation, I'm boggled why my Wait "patch" solution works, lol.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:Kannan K
ID: 37043037
Hi,

I think you should remove the Listbox.visible = false in the list box double click event.

KK,
Private Sub GenericListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'When user double clicks on the listbox value the listbox closes and focus is set to orig text box.

        LookupCellRange.Value = ActiveSheet.OLEObjects("GenericListBox").Object.Value
'        GenericListBox.Visible = False
        SourceTextBox.Activate
        
End Sub

Open in new window

0
 

Author Comment

by:jobprojn
ID: 37043072
dlmille, I believe you're correct.  The issues is with the Double-Click event.  Your 'wait' solution is interesting, only problem is the focus still ends up back in the text box in an awkward sort of way.  I was able to get it to work perfectly with a KeyDown function (see below code).  If there's a KeyCode for double click then I could probably get this thing to work.


Private Sub MyTextBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 120 Then
'When user clicks F9 on this text box the generic listbox fills and displays.

        Set LookupCellRange = Worksheets("Sheet1").Range("P3")
        Set SourceTextBox = ActiveSheet.OLEObjects("MyTextBox")
        GenericListBox.ListFillRange = "luInvoiceNumber"
        GenericListBox.Visible = True
        GenericListBox.Selected(Counter) = True
        GenericListBox.Activate
        
        End If
End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043091
Actually, in my solution - the ListBox is active - you can up/downarrow, but the textbox will still receive text if you type - so may not be awkward as that's where you might want to end up after hitting Enter or Escape in the listbox, right?

Keycodes:  http://msdn.microsoft.com/en-us/library/aa243025(v=vs.60).aspx
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37043105
Ok - add one more routine to your set:

Private Sub MyTextBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        SendKeys ("{F9}")
'alternatively call the KeyDown routine directly with the correct scan code
End Sub

Now when you double-click, the keydown routine is run with F9 and everything works, lol

What a Rube!

Dave
0
 

Author Comment

by:jobprojn
ID: 37043108
You're right, the ListBox is active and up/down arrow works, but if you want to use your mouse to select the value in the list box you have to click in the list box to activate it then you can to select the list value.  So mouse use in the list box gets a bit awkward.

Thanks for the keycodes link.  I found a similar list as well, but oddly keycodes 1 and 2 (mouse clicks) don't work.  My example with keycode 120 (for key F9) works perfectly, it just doesn't like 1 and 2.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043111
Let's add a MOUSE_DOWN and that should get us there.
0
 

Author Closing Comment

by:jobprojn
ID: 37043113
You rock!!  It worked!  Thanks for your help.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043119
Don't we need to add a Mouse_Down?  Or does it really work the way you want, already?  we were cross-posting so I'm lost with your comments.

check again and make sure - let me know.

Dave
0
 

Author Comment

by:jobprojn
ID: 37043137
Dave, for me mouse isn't necessary because I need the KeyDown for the F9 key anyway.  I want to give my users the choice of a double click or an F9 to open up the list box.  With your solution of SendKeys I can end up with the below two pieces of code that works perfectly.

Thanks again for your help.
Private Sub MyTextBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        'When user clicks F9 on this text box the generic listbox fills and displays.
        
        If KeyCode = 120 Then
            Set LookupCellRange = Worksheets("Sheet1").Range("P3")
            Set SourceTextBox = ActiveSheet.OLEObjects("MyTextBox")
            GenericListBox.ListFillRange = "luInvoiceNumber"
            GenericListBox.Visible = True
            GenericListBox.Selected(Counter) = True
            GenericListBox.Activate
        End If
        
End Sub

Private Sub MyTextBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

        'When user double clicks on this text box the above code runs.

        SendKeys ("{F9}")
        
End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043174
;)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question