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

Robert_G

New Member
Joined
Jul 13, 2018
Messages
48
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
fillable%20form.png
 
Upvote 0
Hi,
try following

Place following code in you fillable form worksheet code page

Rich (BB code):
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

Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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**
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top