Double-click on worksheet cell selecting item from Form Listbox

SteveInAlabama

Board Regular
Joined
Aug 22, 2002
Messages
94
When a user single-clicks a cell to Select its row, and then clicks a Command Button, I launch a Form. The form has a Listbox; the SelectionIndex for the Listbox is set via code to default to the correct corresponding item in the Listbox. For example, if the row contains the information to choose the 3rd item in the Listbox (SelectionIndex = 2), that is what is highlighted in the Listbox when the form opens. Works perfect.

I want to also allow the user to Double-click a cell in the desired row, and have the code 'Click the button' for them. However, the double-click seems to carry through to the Form and always selects the item in the Listbox 'underneath' the mouse cursor, trumping the default. From the above example, if the cursor is over item#10 when the form appears, item #10 is selected instead of item #3.

Here are snippets of my properly working code called by the button click:
Code:
Sub SelectDTReasonCode()
    Dim clsDTEvent As ClsM_DTEvent
    Dim frmReasonCodes As Frm_Reason_Codes
    Dim lThisRngRowNmbr As Long
    Dim bFailed As Boolean
  
   'class method to get info to pre-select the Listbox 
    gclsThisShift.GetEventByWksRngRowNumber lActiveCell.Row, clsDTEvent, True, bFailed

    If bFailed Then Exit Sub
    Set frmReasonCodes = New Frm_Reason_Codes
    Set frmReasonCodes.DTEvent = clsDTEvent
    frmReasonCodes.Show
    Set frmReasonCodes = Nothing
End Sub

Here is my double-click code that is (wrongly) also single-clicking on the Form's Listbox:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

        Cancel = True   'Deselect double-clicked cell
        SelectDTReasonCode

End Sub

Application.Undo fails, Application.SendKeys "{ESC}" did not help. I'm stuck. Any ideas?

Thanks in advance!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top