VBA to fill out fields using a specific ID from a table from one sheet to fill out another

[Robert_G]

I have a spreadsheet that I have one sheet that's used as a fill-able form. With a command button it will write specific fields into another sheet for a "data base" in columns A-Z. Each entry is identified with it's own specific ID in column A.

What I would like to do in the fill-able form is to later "retrieve" this information by the specific ID from the data base in a cell & with a command button, basically so I can fill in the form from the data base so I don't have to fill out each line again.

Looking for some suggestions or some code to get me started. I guess what I'm struggling with is how to us the specific ID, search in the database Sheet in column A for the number, then retrieve the information columns B-Z back into another spreadsheet by unique cell numbers in the fill-able.

It's basically reversing my existing code but using a specific ID in a search and fill.

 

[Robert_G]

 

[Robert_G]

 

[dmt32]

Hi,
try following

Place following code in you fillable form worksheet code page

Private Sub CommandButton1_Click()
    Dim Foundcell As Range, DataEntryRange As Range
    Dim Search As String


'get search value
    Search = Me.Range("B3").Value
    If Len(Search) = 0 Then Exit Sub


'define data entry input range adjust as required to match your input form
    Set DataEntryRange = Me.Range("A6:C6,A10:C10")


'find search value in database
    Set Foundcell = ThisWorkbook.Worksheets("Database").Columns(1).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not Foundcell Is Nothing Then
'return database record to your data entry form
    DatabaseToDataEntry DataEntryRange, Foundcell
    Else
'inform user
    MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
End Sub

The Data Entry Range (shown In Red) is based on your example sheet but can be expanded as required.
It is important to note that you need to list the input ranges in the order you want the information returned from your database. So in this example, Database Columns 1,2,3 are returned to Ranges A6,B6,C6. Database Columns 4,5,6 are returned to Ranges A10,B10,C10.

Place Following in a STANDARD module

Sub DatabaseToDataEntry(ByVal DataEntryFormRange As Range, ByVal Target As Range)
'dmt32 Oct 2017
    Dim Cell As Range
    Dim i As Integer
    Dim CellCount As Long
    Dim Data As Variant
    
'count of input cells
    CellCount = DataEntryFormRange.Cells.Count
'create array from range
    Data = Application.Transpose(Target.Parent.Cells(Target.Row, 1).Resize(1, CellCount).Value)


    On Error GoTo exitsub
'turn event code off
    Application.EnableEvents = False
    i = 1
    With DataEntryFormRange.Parent
'step thru each cell in named range
        For Each Cell In DataEntryFormRange
'check if input form range has formula
            If Not .Cells(Cell.Row, Cell.Column).HasFormula Then
'return data to correct cell in form
                .Cells(Cell.Row, Cell.Column).Value = Data(i, 1)
            End If
'increment to next array element
            i = i + 1
        Next Cell
    End With
    
exitsub:
'turn event code on
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Above code is dynamic and only requires that you pass the Data Entry Form input and the Search (Target) Ranges to it. Code will ignore any cell in your input form ranges that contain a formula.

Hope Helpful

Dave

 

[Robert_G]

 

[Robert_G]

Dave,

I get a Compile Error where Sub or Function not defined.

 

[dmt32]

Dave,

I get a Compile Error where Sub or Function not defined.

Did you code code DatabaseToDataEntry to a STANDARD module as I stated?

Dave

 

[Robert_G]

Dave,

I thought that I assigned it to a standard module but maybe the way I’m doing it is wrong. I’ll try again when I’m back on the computer.

 

[Robert_G]

Dave I got it & it works perfectly. Yes, it was my mistake. I didn't follow your directions 100% above. I think that I got it from here. If not, I'll message again. Thanks

 

[Robert_G]

Actually just came across one, this doesn't work on merged cells.

I have a couple cells that are merged.


**edit** Just realized instead of a range if I put in the individual cell, it works **edit**