Generate Quote Report based on User Form?

I have been developing a quoting workbook in which the user is prompted to fill out a user form with all relevant data. The user form then populates a work sheet where further calculations are performed to determine the total cost of a lot of parts based on different labor rates and the cost per part based on lot size.

I would like the button on the user form that populates the worksheet to also generate a quote (.PDF) that is displayed and saved to a folder. Report to include certain user form entry fields and some of the calculated data that happens in the background on the worksheet. Ultimately I would like this quote to be stylized, not in basic Excel formatting. Every time the user form is accessed it should only report on the data entered and the calculated values based off of those entries.

I have little experience with VBA, I have been able to piece together the user form and populating the worksheet (works as intended), I'm just not sure how to get the next step of generating the report.

Any help would be appreciated.

Thanks.

Originally Posted by sigma1113

Ultimately I would like this quote to be stylized, not in basic Excel formatting.

Not quite sure what you mean by this bit.

Originally Posted by sigma1113

Every time the user form is accessed it should only report on the data entered and the calculated values based off of those entries.

Nor this bit.

Perhaps some further information on both points would be helpful.

Also, it may be useful if you could put together something like what you want contents of the PDF to look like. I presume it would be the "Quote Sheet" just turned into a PDF, but as that's blank at the moment it gives us little info to really help you here.

BSB

I suppose what I meant by a stylized quote was that I wanted the formatting to look neat and professional... not just a bunch of cells printed on a page. I wasn't sure if a report, or in this case a quote, template was created in a similar way to a user form or if it is just a formatted work sheet that gets populated.

To your second point, when the user form is filled out it enters all of that information into a single row on the MachiningQuotes Tab, upon entry into that row the report template should be filed in with only the relevant input and calculated data from the newest row.

To keep things simple for now, say I just wanted to report on the Part Number, Lot Size, and Cost Each Columns. I'm looking for the method on how to pull the data from the most recent row of entries for those columns, populate cells on the quote sheet with those data points and then save as pdf and save to a folder. After which the template should be cleared of all data ready for the next user form entry. Once I have the process down I should be able to modify it to the data columns I really need in the quote and will be able to format the template.

Hope this explanation helps a bit.

Thank you for this.

I have gone through your setup and understand most of it. The data entry form and Machining Quoting sheet work as I want but I am still having some issues with the quote sheet. I wanted to change around the quote sheet a little by adding more tables but I'm not sure if I coded them correctly to get them to fill in.

2 changes I would like to figure out how to make:

Have the functionality of the Save and Print button built into the OK button so it will fill the machining quotes table as well as save and print.

I want to save to a local folder on my computer rather than an external USB drive.

Thanks.

One additional note on this issue. The quotation sheet is not being filled. When the OK button is pressed to log the data into the Machining Quotes worksheet the form is cleared and then the Save Print button has nothing fill into the tables. Function of generating the PDF works but all of the fields are blank.

Ok so I am almost there... I just don't understand how to change the file path to a local drive.

I understand that the following bit of code is scanning the drives and looking for a USB drive. Once found, it assigns the drive letter to MyDrive2. MyDrive2 is then used in the second bit of code to assign the file location.

Since I will save locally, for instance to "C:\Users\XXXXX\Desktop\Machining Quotes", I do not need to scan for a USB drive (perhaps just scan to make sure that folder is present?) how do I change the value of MyDrive2 to the file path of the folder I am saving to? or is there a different method for this?

Thank you for all of your help thus far.

Set FSO = CreateObject("Scripting.FileSystemObject")
For Each Drv In FSO.Drives
With Drv
If .IsReady And .DriveType = 1 Then
decision = MsgBox("Detected USB Removable storage device in drive " & .DriveLetter & vbCrLf & "Do you wish to continue ?", vbQuestion + vbYesNo, "Save ?")
If decision = vbNo Then Exit Sub
MyDrive2 = .DriveLetter
GoTo SaveRoutine
End If
End With
Next Drv
MsgBox "No USB Removable storage devices found", vbInformation
Exit Sub

Sheet4.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyDrive2 & ":\" & tbl1.Range(a, 2) & " " & Format(Now(), "MM-DD-YYYY") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Thank You, the save functionality now works as intended.

The last thing I am trying to do is add hyperlinks to the file paths.

On the user form I created a DrawingFilePathCommandButton for the user to select a file from the computer and then it inserts that file path into the DrawingFilePathTextBox.Value, this portion of the code works properly.

Private Sub DrawingFilePathCommandButton_Click()
    Dim s As String
    s = Application.GetOpenFilename
    DrawingFilePathTextBox.Value = s
End Sub

Under the OKButton_Click Sub, I want to store DrawingFilePathTextBox.Value (the drawing filepath from above) as a hyperlink in .Range(31).Value so when it is stored into the MaciningQuotes Worksheet it displays and behaves as a hyperlink. I tried the following bit of code but I get a Run Time Error 5.

ActiveSheet.Hyperlinks.Add Anchor:=.Range(31).Value, Address:=DrawingFilePathTextBox.Value, TextToDisplay:=PartNumberTextBox.Value

Finally that Hyperlink should be present on the saved PDF as well.

Thanks.