I've made, i think a minuscule amount of progress, although I'm still seriously struggling. Here is a copy of my user form:
but I can't get the code to work. Here's what I've tried so far but without success:
Private Sub CommandButton8_Click()
Dim RecordRow As Long
Dim RecordRange As Range
' Turn off default error handling so Excel does not display
' an error if the record number is not found
On Error Resume Next
' Find the row in the table that the record is in
RecordRow = Application.Match(CLng(TextBox184.Value), Range("Table4[Record]"), 0)
' Set RecordRange to the first cell in the found record
Set RecordRange = Range("Table4").Cells(1, 1).Offset(RecordRow - 1, 0)
' If an erro has occured i.e the record number was not found
If Err.Number <> 0 Then
ErrorLabel.Visible = True
On Error GoTo 0
Exit Sub
End If
' Turn default error handling back on (Let Excel handle errors from now on)
On Error GoTo 0
' If the code gets to here the record number was found
' Hide the error message 'Not Found'
ErrorLabel.Visible = False
' and populate the form fields with the record's data
TextBox66.Value = RecordRange(1, 1).Offset(0, 1).Value
TextBox67.Value = RecordRange(1, 1).Offset(0, 2).Value
TextBox68.Value = RecordRange(1, 1).Offset(0, 3).Value
TextBox69.Value = RecordRange(1, 1).Offset(0, 4).Value
End Sub
The first four worksheets have been 'converted' into tables "sheet 1 = table 4, 2 = table 3, 3 = table 2 and 4 = table 1. Show form, print and close all work but that's it! As you can see, I've tried the first four text boxes...
Your kind help will be invaluable!
Mel