CODEWITHSUNDEEP
excelvba
Danny
Danny

Reputation: 89

Double click event to fill textbox in form

I am trying to use double-click event of worksheet to populate a textbox in a form with value from a specific column in the double-clicked row.

e.g.

I have four columns 

ID Name Age Gender
1  A     24  M
2  B     26  F
3  C     22  F
4  D     30  M

When I double-click on any cell in the column Name, a form with a textbox will pop up with the textbox filled with the value from the column Gender in the row which was double-clicked. SO when I double-click on "B" in column Name a form should pop up with textbox value "F".

this is my code so far

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, CANCEL As Boolean)
    CANCEL = True
    If Target.Column = 2 Then
        Update.Show
        With Update.TextBox1.value = ?????
        End With
    End If
End Sub

Upvotes: 1

Views: 2747

Answers (2)

Danny
Danny

Reputation: 89

I used the double click event to open the form & form activate event to fetch the value from the column in the active cell row

so its basically

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, CANCEL As Boolean)
    CANCEL = True
    If Target.Column = 2 Then Exit Sub
    Update.Show
End Sub

Private Sub UserForm_Activate()
Dim r As Long

    r = ActiveCell.Row

    Me.TextBox1.value = Cells(r, 4).value

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

Using a form called frm_Update with three appropriately named text box controls.
This will take the value from the Target row, columns 2, 3 and 4 and place the values in the textboxes:

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

    Dim oForm As frm_Update

    If Target.Column = 2 Then
        Set oForm = New frm_Update

        Cancel = True
        With oForm
            .txtName = Cells(Target.Row, 2)
            .txtAge = Cells(Target.Row, 3)
            .txtGender = Cells(Target.Row, 4)
            .Show
        End With
    End If

End Sub

@Comintern - I'm going to have a good read of that link. All stuff I really should put into practice.

Upvotes: 2

Related Questions