Event Worksheet_BeforeDoubleClick provides values from previous double-click

Question

I have a table of people with work to assign to:

I want to use the BeforeDoubleClick Event to invoke a form showing all the relevant fields, so that the user can consult/correct the fields.

The form looks like this:

In the corresponding worksheet, I have the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("tblTime")) Is Nothing Then
        Cancel = True
    
        frmAssignNewWork.Show
    
        frmAssignNewWork.Controls("cboPerson").value = Cells(Target.Row, 2).value
        frmAssignNewWork.Controls("cboWork").value = Cells(Target.Row, 4).value
        frmAssignNewWork.Controls("txtStartDate").value = Cells(Target.Row, 5).value
        frmAssignNewWork.Controls("txtEndDate").value = Cells(Target.Row, 6).value
        frmAssignNewWork.Controls("txtFocus").value = Cells(Target.Row, 7).value * 100

It works somewhat, as it shows the form, and populated fields in the right place, BUT it populates the form with the fields that were selected on the PREVIOUS double-click.

To clarify, if I double-click a first time, the form will appear with blank fields.
Then, if I double-click a second time on the same cell, it will display the correct information of that row in the correct fields.

However, if I double-click a third time on a different row, it will display the information of the former row, etc.

I would think it has to do something with the concept of "Before" double-click, but if I insert a check such as:

MsgBox (Target.Address)

It does always provide the correct address...

Anyone can help out there? :)

Put `MsgBox "!"` right after the `frmAssignNewWork.Show`. That will hopefully explain it. — GSerg, Jul 05 '21 at 16:37
Try to move `frmAssignNewWork.Show` to the end of the procedure — Алексей Р, Jul 05 '21 at 16:37
Thank you Алексей Р, this worked!I would have assumed that setting the value of the fields on the form would refresh them, but apparently not :). I also believed wrongly that I had to show the form before being able to update the fields — MoonLunatic, Jul 05 '21 at 17:04
@MoonLunatic Yes it does refresh them. It didn't for you because that code does not run [until you close the form](https://stackoverflow.com/a/1784410/11683) in the first place, which you would have seen if you had put [the `MsgBox`](https://stackoverflow.com/questions/68259325/event-worksheet-beforedoubleclick-provides-values-from-previous-double-click#comment120638746_68259325). Otherwise it's perfectly possible to change values on an already shown form. — GSerg, Jul 05 '21 at 17:12

score 0 · Answer 1 · answered Jul 05 '21 at 17:05

0

Move frmAssignNewWork.Show to the end of the procedure solved it

answered Jul 05 '21 at 17:05

MoonLunatic

1

Event Worksheet_BeforeDoubleClick provides values from previous double-click

1 Answers1