Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i re-populate a userform with data already entered? | New Users to Excel | |||
Populate a cell with a date using calendar pick | Excel Discussion (Misc queries) | |||
create a userform to populate a diagram with the forms info | Excel Discussion (Misc queries) | |||
Userform using 3 ranges to populate. | Excel Programming | |||
Range of Cells to populate in a text box on a userform | Excel Programming |