Pick cell and populate userform
 

Here's what I'm trying to do: I have a userform that contains a textbox and a commandbutton. The user would have the option of either to type the data required in the text box or to click the commandbutton to hide the userform and look for the data in one of the sheets of the worksheet. Once the desired data is found in the sheet, the user would click (or doubleclick) on that cell and then the userform textbox would be populated with the contents of that same cell. Is there a way to do something like this? I appreciate any help from the Excel gurus..

Thanks, Alex.

Pick cell and populate userform
 

You want a RefEdit control, not a Textbox.

Create a new userform, drop a RefEdit control onto it. VBA will hopefully
name it RefEdit1. Add this code:

Private Sub RefEdit1_Change()
Static blnUpdating As Boolean
Dim rng As Range

If Not blnUpdating Then
On Error Resume Next
Set rng = Range(RefEdit1.Text)
If Not Err.Number Then
blnUpdating = True
RefEdit1.Text = rng.Value
blnUpdating = False
End If
On Error GoTo 0
End If
End Sub


You wont be able to type text which resolves to a Range. eg. Typing A1 will
end up copying the Value in cell A1 instead.
Let me know if that's going to be a problem?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Alex" wrote in message
...
Here's what I'm trying to do: I have a userform that contains a textbox
and a commandbutton. The user would have the option of either to type the
data required in the text box or to click the commandbutton to hide the
userform and look for the data in one of the sheets of the worksheet. Once
the desired data is found in the sheet, the user would click (or
doubleclick) on that cell and then the userform textbox would be populated
with the contents of that same cell. Is there a way to do something like
this? I appreciate any help from the Excel gurus...

Thanks, Alex.