Solved: Double Click a cell to show form with data

[sujittalukde]

I have attached a file which containd a button "Show me". On clicking the button, a user form is shown. But it always contain the first name in the combo box in the user form.

Now I want that if I click a cell in the column A then the combobox should contain the person name in that cell. For eg., if I call the name "BISWA" the user form should open up and the combo box should contain the name of the "BISWA" and the text boxes , the appropriate data.

How this can be done?

[georgiboy]

Now i cant remember what i changed so here is all of it
[vba] Private Sub ComboBox1_Change()
Dim Pos As Long

With Sheets("zz cc")
'If ComboBox1.Value = "" Then
'Exit Sub
'Else
Pos = Application.Match(ComboBox1.Value, .Columns(1), 0)
TextBox1.Value = .Cells(Pos, 2).Value
TextBox2.Value = .Cells(Pos, 3).Value
'End If
End With

End Sub

Private Sub CommandButton1_Click()
Dim Found As Boolean
Checkname = ComboBox1.Value
Found = False
i = 1
Do While Found = False
employeename = ActiveCell.Value
If Sheets("zz cc").Range("A" & i + 1).Value = "" Then Exit Do
If employeename = Checkname Then
Sheets("zz cc").Range("A" & i).Select
Found = True
ActiveCell.Offset(0, 1).Value = TextBox1.Value
ActiveCell.Offset(0, 2).Value = TextBox2.Value
ComboBox1.ListIndex = i - 1
End If
i = i + 1
Loop
'endo:
'MsgBox "Last REcord"
'ComboBox1.SetFocus

End Sub

Private Sub Label1_Click()
a = ComboBox1.ListCount
MsgBox a
End Sub

Private Sub Label2_Click()
a = ComboBox1.ListIndex + 2
MsgBox a
End Sub
Private Sub UserForm_Activate()

Dim R As Range 'The Range of the Names
Dim LastCell As Range 'The cell of the last Name in column 1
Label1.Caption = ActiveCell.Value
Label2.Caption = ActiveCell.Offset(0, 1).Value
Label3.Caption = ActiveCell.Offset(0, 2).Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Find the Range of Names and assign it to the ComboBox List
'This way your ComboBox will show only the names
'without the extra blanks of your range A2:A446
'Go to the ComboBox Properties window and delete the RowSource =nemp
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Sheet1
Set LastCell = Columns(1).Find( _
What:="*", _
After:=[A1], _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Not LastCell Is Nothing Then
LastRow = LastCell.Row
Else
MsgBox "No names found", , "Message"
Exit Sub
End If
Set R = .Range(.Cells(2, 1), .Cells(LastRow, 1))
End With
'Give a name to the Range of names
R.Name = "Names"
'Assign the Range to the ComboBox
Me.ComboBox1.RowSource = "Names"
ComboBox1 = ActiveCell.Value
ComboBox1.SetFocus
'Label1.Caption = ThisWorkbook.Sheets(1).Range("A1").Value
'Label2.Caption = ThisWorkbook.Sheets(1).Range("B1").Value
'Label3.Caption = ThisWorkbook.Sheets(1).Range("C1").Value
'ComboBox1.ListIndex = 0
'ComboBox1.SetFocus
End Sub
[/vba]

also put this in your zz cc worksheet
[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
UserForm1.Show
End Sub[/vba]

then double click on the name

Hope this helps

[sujittalukde]

Thanks georgiboy, its working well. Now I'll try the same in my own final project and if any problem found, I'll come back.
Thanks again.