Populate Form and Print

[sugiyo]

I'm halfway home, I just can't find what I need to finish this project.

I have created a user form that takes ten strings (dates, numbers, sentences) and stores them in the next available row in the sheet "Equipment Repair Log" Yay.

NOW I want to move over to the next worksheet "Repair Log Printing" hit my macro button have the form ask me which entry I would like to print. got that so far, I pick the entry say...row 57 press enter and, here's the hard part I can't figure out... The userform goes to line 57 of "Equipment Repair Log" grabs column A puts in cell B1 of "Repair Log Printing". Puts column B into G1 etc, etc, until my form is populated and I can hit print.

Most of the time I'll never need the data in hard copy form, but when I need to print off a record I'd like to be able to simply pick it from the list.

If you want me to send you what I have shoot me a message.

 

[Norie]

What is the part you can't figure out?

The printing?

 

[sugiyo]

The userform goes to line 57 of "Equipment Repair Log" grabs column A puts in cell B1 of "Repair Log Printing". Puts column B into G1 etc, etc, until my form is populated

The above part doesn't happen, only in my mind

 

[Norie]

Oh I though that was happenning.

So you want to populate the worksheet to be printed with the data from whatever row you selected.

That should be straightforward - just use the same sort of code you used to put the data from the form on the Equipment Repair Log worksheet

Then just print the worksheet.

Worksheets("Repair Log Printing").PrintOut Copies:=1

 

[sugiyo]

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Equipment Repair Log")
 
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
 
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.x1.Value
ws.Cells(iRow, 2).Value = Me.x2.Value
ws.Cells(iRow, 3).Value = Me.x3.Value
ws.Cells(iRow, 4).Value = Me.x4.Value
ws.Cells(iRow, 5).Value = Me.x5.Value
ws.Cells(iRow, 6).Value = Me.x6.Value
ws.Cells(iRow, 7).Value = Me.x7.Value
ws.Cells(iRow, 8).Value = Me.x8.Value
ws.Cells(iRow, 9).Value = Me.x9.Value
ws.Cells(iRow, 10).Value = Me.x10.Value

This comes from the front end, each text field in the userform was labeled x1 - x10 and then whatever was inputed get stored in the worksheet "Equipment Repair Log"

I just don't understand the code enough to make it do what I want it to do on the other side. Go to the line I request, store the data again and bring it up somewhere else, I can happily press print manually, I don't even care about that.

 

[Norie]

Can you clarify what you do and don't have?

Also, how do you want all this to be done?

Do you want to do it via the userform?

 

[sugiyo]

Yeah, sorry I'm making this confusing. I'll lay it out for you as best as I can.

The excel file worklog.xls has three sheets in it: Repair Log Entry, Equipment Repair Log, and Repair Log Printing

In the first one, there is nothing but a Macro button which starts a userform. The userform has 10 fields <x1,..., x10>, and two buttons. The first button starts a command that finds the next available row in the Equipment Repair Log worksheet, puts the data <x1,..., x10> in that row, and finally clears the fields so you can add another entry. The second button closes the form.

In the second worksheet, Equipment Repair Log, there is just data. There is a header row detailing what those 10 columns are and after that is just row and row of data input by the form.

In the third worksheet, Repair Log Printing, I have a printer friendly (good looking) page with no details on it, but labels and places for info to go.

THAT is what I have.
THIS is that I want.

I want to put a macro button on the Repair Log Printing page which will sit outside of the printing range so it doesn't show up on paper. When you click on said button it will call a userform with a single field to enter data into, and two buttons. In the field I will enter the number of the row from the Equipment Repair Log worksheet which contains the entry I need to have a hard copy of (lets pretend row 453). I'll press enter and it will grab those first ten cells in row 453 on that worksheet and put each one in the appropriate cell I designate in the printing (good looking) worksheet.

The other button will close the userform

I will happily email you work log.xls if you private message me your email. I hope this helps

 

[Norie]

I'll PM you my email.

Couple of things though, you can set a button to not print so it can be placed in printing range, if it's more suitable of course.

Also, why not use the form you already have?

Anyway, I'll PM you.