VBA - Auto fill a template in excel

[mageeg]

Hi, i have created a very simple spreadsheet to demonstrate what i am after.

Lets say i have a sheet in excel called "Data"

I then have another sheet called "Template" this is my template i want to fill in. For the sake of the explanation i have created a very simple one.

I then want to have a form like the below, in which the user enters the ID No (as it is unique) presses print and then it pulls the data from that row and fills the template above.

How would i go about doing this? I would like the user to enter the ID number, and pull the data from that row into the correct position on the template sheet.

 

[Fluff]

Are you happy to use a combobox, rather than a textbox? It makes things a lot easier & helps prevent typos.

 

[mageeg]

Are you happy to use a combobox, rather than a textbox? It makes things a lot easier & helps prevent typos.

Hi, I did think about having a combo box

The issue is as time goes on there are going to be a lot of data entries in the database. This means there could be over 100 rows in which looking through a combo box could be painful

 

[Fluff]

OK, are you IDs actual numbers or text?

 

[mageeg]

OK, are you IDs actual numbers or text?

My ID’s are stored as text

 

[Fluff]

Ok, how about

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim Fnd As Range
   
   Set Fnd = Sheets("Data").Range("A:A").Find(Me.TextBox1.Value, , , xlWhole, , , , , False)
   If Fnd Is Nothing Then
      MsgBox Me.TextBox1.Value & " not found"
      Exit Sub
   End If
   With Sheets("Template")
      .Range("B2").Value = Fnd.Offset(, 1).Value
      .Range("B4").Value = Fnd.Offset(, 2).Value
   End With
End Sub

 

[mageeg]

Ok, how about

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim Fnd As Range
  
   Set Fnd = Sheets("Data").Range("A:A").Find(Me.TextBox1.Value, , , xlWhole, , , , , False)
   If Fnd Is Nothing Then
      MsgBox Me.TextBox1.Value & " not found"
      Exit Sub
   End If
   With Sheets("Template")
      .Range("B2").Value = Fnd.Offset(, 1).Value
      .Range("B4").Value = Fnd.Offset(, 2).Value
   End With
End Sub

This is the sort of thing, i am looking for. That's great thanks!

just a question, if for example i wanted to put first name and second name together so had a box on the template called "Full Name", how would i join these two fields together in the above code?

 

[Fluff]

Like

.Range("B2").Value = Fnd.Offset(, 1).Value & " " & Fnd.Offset(, 2).Value

 

[mageeg]

This is the sort of thing, i am looking for. That's great thanks!

just a question, if for example i wanted to put first name and second name together so had a box on the template called "Full Name", how would i join these two fields together in the above code?

That's amazing thank you!

Lastly, I have set a print area for this label, can i possibly add a piece of code to the end of this, which print the template once it is filled in?

 

[Fluff]

As that's a totally different question, it needs a new thread.
Thanks