Activating userform and filling it with data form row where userform is activate

[Marthijn Beusekom via OfficeKB.com]

I use a userform to mutate data in a sheet. I would like this userform to
be activated by a double click or press of button. The hard part is that I
would like the fields in the userform to be filled with data mentioned on
the selected row.

For example:

Cust.nr. Name phone age
1 Jan 1234567 20
2 Peter 1388431 25
3 Elco 3215687 30


I like peter's record to be mutated, so I would like to double click on
‘Peter’ to activate the userform. In this userform I want Peters current
data already mentioned.

My attempt of adding a button left of each row does not work. It does of
course activate the userform but there is no record mentioning that ‘button
on row 2’ was pressed. So the userform can not be filled with the data on
row 2.

I am out of clues. Can you help me?

 

[Harald Staff]

Hi

Put something like this in your userform module:

Public Sub FillMe(R As Long)
TextBox1.Text = Cells(R, 1).Value
'and so on
End Sub

and something like this in your worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Call UserForm1.FillMe(Target(1).Row)
UserForm1.Show
End Sub

HTH. Best wishes Harald

 

[Tushar Mehta]

I am curious why you think there is a need for a form when XL does a
pretty good job of handling data entry. Clearly, the user has access to
the worksheet so why won't they edit the worksheet directly?

Also, if you must, check out XL's native data form, rudimentary as it
is: Data | Form...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Marthijn Beusekom via OfficeKB.com]

Thanks Harald,
you pointed me in the right direction