How to view cell detail, on double-click, in form?

Question

I want when a user double-clicks column 5 or 6 on a worksheet, to open a form with two textboxes.

One textbox shows the value in column 5 for the current row, and the other shows the value in column 6 for the current row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo ErrorHandler

Dim FISHRef As String
Dim baconRef As String
Dim rowNum As Long
 
Dim FISHCell As String
Dim BACONCell As String
 
If Not IsError(Application.Match(ActiveCell.Column, Array(5, 6), False)) Then
 
    rowNum = ActiveCell.Row
    FISHCell = "E" & CStr(rowNum)
    BACONCell = "F" & CStr(rowNum)
    'Range(FISHCell).Calculate
    'Range(BACONCell).Calculate
    
    FISHRef = Sheets("ReferenceCompare").Range(FISHCell).value
    baconRef = Sheets("ReferenceCompare").Range(BACONCell).value
 
    If FISHRef = "" And baconRef = "" Then
        Exit Sub
    End If
    
    frmRefs.Show
    frmRefs.txtFISHRef = FISHRef
    frmRefs.txtBACONRef = baconRef
        
End If
        
ErrorHandler:
    Exit Sub
    
End Sub

Both of the cells on the worksheet are complicated calculations involving some text functions that I know work.

When the form opens, it puts the values of the cells from the previous double-click. Is it in the code or is it not fast enough to calculate?

Answer 1

You are showing the form in modal form (the lines after frmRefs.Show never execulte until you unload the form. You need to update these textboxes before you show the form like this:

    frmRefs.txtFISHRef.Text = FISHRef
    frmRefs.txtBACONRef.Text = baconRef
    frmRefs.Show