Button to move data from current row to a pdf

[JRSone]

We collect Permit information for each customer once their project begins. Basic info is recorded from left to right all in one row. Name, address, equipment type, installation date, etc. About 12 columns of data, all as text or a number treated as text like a zip code or permit number. There are no mathematical calculations needed. The only other data types are dates in the mm/dd/yy format. Throughout the process of the project, more info is added to the customer’s row as it progresses towards completion. Relatively simple database. What I would like to do is insert a button in each row, in a designated column, that will take data from five or six cells within that same row and put them on the next Sheet which I have set up as a form letter. I need the “click” of the button to show me the now populated form letter so I can manually make any adjustments, then save it as a PDF and put it in our customer’s folder on a shared drive. Each row is a different customer with no limit on number for the year. List could be 200 customers long or 300 customers long depending on the year. I do not want to make 200+ macros so I can have the button grab the correct cells for each row.

 

[Candyman8019]

Assuming your form letter is built in excel and not already a pdf itself you could use something quick and dirty like this:

Private Sub PopulateAndSaveForms()
Dim fname As Variant

    Sheet2.Range("A1").Value = Sheet1.Range("D" & Selection.Row).Value
    Sheet2.Range("A2").Value = Sheet1.Range("E" & Selection.Row).Value
    Sheet2.Range("A3").Value = Sheet1.Range("F" & Selection.Row).Value
    Sheet2.Range("A4").Value = Sheet1.Range("G" & Selection.Row).Value
    Sheet2.Range("A5").Value = Sheet1.Range("H" & Selection.Row).Value
    Sheet2.Range("A6").Value = Sheet1.Range("I" & Selection.Row).Value

fname = Application.GetSaveAsFilename(filefilter:="PDF Files, *.pdf")
If fname <> False Then Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, quality:=xlQualityStandard, IncludeDocProperties:=True, Ignoreprintareas:=False, openafterpublish:=False
    
End Sub

This way you only need one button at the top of your sheet and it will work with whatever row of data you have selected at the time.

 

[JRSone]

Thank you for responding Candyman! Yes, this letter is built in Excel. It is a simple form letter which lets customers know which phone number to call, what info to provide and when to do so. My thought was to have a button at the end of each customer's row, which when clicked, would take their name from column b, their address from column c, their permit number from column h, etc. Then put that into the form I set up on the next Sheet. In your example above, "Sheet2.Range("A1").Value" etc. would always be the same because they will always refer to those cells in my form, right? But the other items, "D", "E", "F", etc., would depend on which button I clicked and in which row that button was placed. Right? I appreciate your patience with this, I am very new to VBA

 

[Candyman8019]

You could put a button at the end of each line, but that would be a lot of work...especially if you expect to have hundreds of entries. Even if you chose to go that route, each of the buttons would trigger the same set of code. I have provided a link to an example workbook here: formletter.xlsm

In this case, the cell you have selected will be the row of data that is used when you click the generate button.

Joe

 

[Candyman8019]

The key here is rather than using absolute cell references it uses selection.row to take data from whatever cell/row is selected when the button is pressed.

 

[JRSone]

This is starting to make sense! The code above will populate my form just as needed. Instead of "selection.row to take data from whatever cell/row is selected when the button is pressed", can it be based on the position of the particular button that was clicked? If I click in a random cell off to the side, then click the button, it just keeps printing info from the row where the random clicked cell is located and not the row where the button is located. I gather, that is exactly what "selection.row" is supposed to do so I think I am asking if there is a different term to use to get the decision based on button location and not "last clicked cell" location.
Also, how can I see the form letter before I give it a name and save it to a folder? Just in case I need to make minor adjustsments?

 

[Candyman8019]

The whole purpose of this method is so you don't need to create multiple buttons. You could replicate the macro multiple times and have one for each button if you wished to do it that way.

If you want to see the form before you save it, you will need to split the code up and have one macro to populate the six cells...and the second to save as pdf. When you run the macro to populate your cells, you can add this line to the code.

sheet2.activate

Assuming your form letter is on sheet 2.

so it would end up looking something like this:

Private Sub PopulateForm()

'populate cells on form letter
    Sheet2.Range("A1").Value = Sheet1.Range("D" & Selection.Row).Value
    Sheet2.Range("A2").Value = Sheet1.Range("E" & Selection.Row).Value
    Sheet2.Range("A3").Value = Sheet1.Range("F" & Selection.Row).Value
    Sheet2.Range("A4").Value = Sheet1.Range("G" & Selection.Row).Value
    Sheet2.Range("A5").Value = Sheet1.Range("H" & Selection.Row).Value
    Sheet2.Range("A6").Value = Sheet1.Range("I" & Selection.Row).Value

'Take the user to the form letter sheet
sheet2.activate

end sub

and your second macro then would be:

Private Sub SaveForm()
Dim fname As Variant

fname = Application.GetSaveAsFilename(filefilter:="PDF Files, *.pdf")
If fname <> False Then Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, quality:=xlQualityStandard, IncludeDocProperties:=True, Ignoreprintareas:=False, openafterpublish:=False
    
End Sub

 

[JRSone]

This will work just fine, Thank you! I can put a "dummy" column in there with each row containing the text "Correct Customer?" So they have to click on that cell first and then click the actual button and now the correct row would be chosen to make the macro give me the row I intended.

Next Step will be figuring out how to put some of that data into another form which was already created with Adobe Acrobat as a fillable PDF.

OOOH, wait. I can hide the button and have it unhide once you click on the cell that says "Print Anderson's Letter"? Could that work?

 

[Candyman8019]

Yes, you can hide buttons...for example: btnButton1.visible = false

for PDFs, I typically import them into a tab of the spreadsheet and put labels over each place I want to place values from my spreadsheet. Then you can do similar to what you did above to populate the values, such as:
sheet11.txtLabel1.caption = sheet1.range("D" & selection.row).value

 

[JRSone]

You mean just insert the PDF as a picture and then put labels over the fields I want to place my values?