How do I poulate Userform text boxes from a sheet of data?

[Barquois]

Hi,

I really don't know where to begin with this, so I'll just give a brief summary of what I'm trying to achieve.

Basically I have a tabbed Userform which contains many textboxes for pieces of information. When this Userform is populated and the OK button is pressed it finds the next blank row in Column and inputs the data from the userform input boxes across the cell ranges A through to Y.

What I want to do is incorporate a retrieve and edit function based upon the reference in cell A, so it effectively reads a second input box (off a button) finds that reference and repopulates the text input boxes in the userform for edit and subsequent resubmission to the form.

Is this super complicated to do?

I've made all the forms I just can't work out how to piece them altogether.

Any help guidance is most welcome!!

Regards

Nick

 

[Trevor G]

Nick are you using a combo box to show all the items in Column a and when you select something it then needs to populate based on the row of data it finds? How many columns are you using..

So perhaps selecting a list of names in Column A then populate the combo

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cboNames_Change()<br>Me.txtTopics = Me.cboNames<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>Sheets("Topics").Select<br>Range("A1").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>Me.cboNames.AddItem ActiveCell.Value<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Barquois]

This is the submission routine (cut down a bit)

Private Sub OKButton_Click()
'Make Main Active
Sheets("Main").Activate
'Determine emptyRow
Dim r As Long
' find last used cell in Column A
  r = Cells(Rows.Count, "A").End(xlDown).Row
 
'Export Data to worksheet
Cells(r, 1).Value = RefNumber.Value
Cells(r, 2).Value = ReviewDate.Value
Cells(r, 3).Value = StartTime.Value
Cells(r, 4).Value = ReviewerListBox.Value
Cells(r, 5).Value = ReasonForReviewListBox.Value
Cells(r, 6).Value = ForenameTextBox.Value
Cells(r, 7).Value = surnameTextBox.Value
Cells(r, 8).Value = DateofbirthTextBox.Value

The 'values' populate from the corresponding text/list boxes on the userform.

What I then want to be able to, once submitted is to recall these pieces of data into each text/list box for edit and resubmission (if there needs to be any changes made to the information.)

This is as far as I have got with the retrieve function

Sub RetrieveButton1_Click()
Dim Search As String
Search = InputBox("Please enter Reference", "Retrieve Record details")
If Search = vbNullString Then Exit Sub
Cells.Find(What:=Search, After:=Cells("A:A"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
 
End Sub

I want to be able to retrieve the data by the reference in column.

Does that make more sense?

Regards

Nick

 

[Noz2k]

Ok, assuming refnumber is unique and in column A try this

firstly make refnumber a combobox

then on userform initialize type

Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
    If lngLastRow <> 2 Then
        refnumber.List = Worksheets("Main").Range("A2:A" & lngLastRow).Value
    Else
        refnumber.List = Worksheets("Main").Range("A2:A3").Value
    End If

then on the change procedure of the refnumber combobox do something like the following.

If refnumber.ListIndex <> -1 Then
 
        reviewdate.Value = Range("A" & refnumber.ListIndex + 2).Offset(, 1)
        starttime.Value = Range("A" & refnumber.ListIndex + 2).Offset(, 2)
 
End if

You might need to play around with the numbers if it references the wrong row, and you'll need to add the data for all corresponding rows but I think that's the basis