Can I initialize a uForm via Button, with several textboxes populated from the currently selected row?

[aubullion]

I have some coding experience, but never attempted anything like this in Office 2010.

Essentially I have an excel worksheet functioning as a database of service records. Nearly 10K rows/records, with 'Customer Name, Contact, Phone, Invoice #, Product ID, Model, Desc, Make, Lot#, Service Date, Pass/Fail' as the column headers (A to K).

Our current system involves entering the this data into a premade MSWord 2010 form for customer records and then copying the same data into the database worksheet. These steps don't always happen back to back, and that's when the database starts to fall behind / records never make it in / not functioning like it should.

To avoid this, I set about figuring out how to automatically generate the Customer Record form, from a record/row of the database, to force/reward users for entering it in the Excel DB first. This led me to this website and learning about the UserForms / VBA / etc. etc.

I have now generated the template for the userform I would like to be automatically populated, but I am stuck on a few things:

1) How do I call this form from the database worksheet? I am aware I could use a button, but with 10,000 rows of data, it wouldnt be convenient to hide it on the worksheet physically, is there a floating button option?

2) What would the actual code need to be so that when a row is selected, it grabs the values from the appropriate columns for that row (A : K), pass that data to the corresponding textboxes on the userform (literally name "txtA to recieve data from column A", txtB for column B data, etc.), and present the user with the "auto-filled" form.

3) The user needs to print the filled out the form in a physical copy/landscape, if this possible with a userform? The whole exercise becomes pointless if this isnt possible and I am not sure if it is (all forum posts i came across via google where from 2004-2006).

I may have started going about this in completely the wrong way, set me straight if so. THanks in advance, (Sorry I didnt know how to attach files or I would attach the actual files for use. Please see images below for best I can do to illustrate.

Ex of Database/Worksheet Layout

Screengrab of UserForm

 

[andrewkard]

Hello,
"How do I call this form from the database worksheet?"
You can insert module with code:
Sub ShowUserForm1()
UserForm1.show
End Sub
And assign button on it.

 

[andrewkard]

"pass that data to the corresponding textboxes"
UserForm1.TexBox1=Range("A1").Value

 

[andrewkard]

"The user needs to print the filled out the form"

Sub ShowUserForm1AndPrint()
UserForm1.Show 0
UserForm1.PrintForm
End Sub

 

[aubullion]

UserForm1.TexBox1=Range("A1").Value

EDIT: Just going to reply to all posts in one response (dont know why you answered in 3 separate).

#1: Can that code be put into a floating button or a button on the toolbar that is always present?

#2: I'll admit I dont understand this completely yet, but this doesnt seem enough to solve my issue. It is simply going to link A1 data to Txtbox1 every time the UF is opened. It will not be dynamic to the row the cursor is on. Depending on what row the cursor is on, for instance, row 26, I want to click the macro button and have the A26 : K26 data transposed into their corresponding textboxes txtA : txtK on the Userform. If the user moved the cursor to row 5646 on the worksheet and clicked the macro button again, it would open the UserForm populated with A5646 : K5646.

#3: I need it to print in landscape. WHere does that code go? I would prefer there is an opportunity to review the generated UserForm before printing, which I feel is not possible with your example.

Again, Im not trying to sound ungrateful, but each point still has outstanding issues, that were not addressed.


Thank you,

 

[andrewkard]

Hello, not the same what you want but this file shows you general line how to do this.
Лист Microsoft Office Excel.xls — RGhost — файлообменник
Please try.