Chart picture vs camera tool - Which to use?

[Mr_Ragweed2]

Hello all! I have been reading posts here and online and i'm not sure which path to try. I have a Userform with many buttons that lead to various "Orderforms" - 1 per vendor. What my macro does is capture the info from each vendor onto 1 "Summary" Sheet and 1 "customer info" sheet. (I guess i could technically combine the data into 1 sheet behind the scenes.) What i would like to do is put the data from both of those sheets back onto the userform when the user is finished, and then make the userform printable. I have set aside space on the form to house this. Which is better/easier (I redefine novice every time i use VBA), chart picture or camera tool? Will i be able to "choose" where i place it on the userform?
I will happily share some code and/or screenshots of the Userform - just didn't want to waste anyone's time if this is not even possible.
Even if there is no help here, but you're aware of something you've seen - all help is greatly appreciated.

Total width of data is 12 columns and total number of rows (including headers and blank rows) is dynamic from 8 to 21. So not a large table/chart/picture.
I'm on Windows 10, Office 365
Thank you very much!

 

[Mr_Ragweed2]

Sorry for the late response, i was in a meeting all day.

I put it in the new line, changed SheetName to "Combined data", commented out the old line and get an "the specified value is out of range' error on the new line.

Public Sub CreateJpg(SheetName As String, xRgAddrss As Range)
'Userform code ****adjust Userform1 & Image1 to suit
'creates temp JPG file of range (xRgAddrss) by creating temp chart
'uses current wb sheet (sheetname) to locate temp chart
'To operate:  Call CreateJpg("Sheet1", Sheets("Sheet1").Range("A1:H8"))
'adjust sheet name and range to suit
Dim xRgPic As Range
Worksheets("Combined data").Activate
Set xRgPic = xRgAddrss
xRgPic.CopyPicture

With Sheets("Combined data").ChartObjects.Add(xRgAddrss.Left, xRgAddrss.Top, xRgAddrss.Width, xRgAddrss.Height)

'With ThisWorkbook.Worksheets("Combined data").ChartObjects.Add(UserForm1_MasterSeedOrderForm.Image3.Left, _
                UserForm1_MasterSeedOrderForm.Image3.Top, UserForm1_MasterSeedOrderForm.Image3.Width, UserForm1_MasterSeedOrderForm.Image3.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "" & "TempChart.jpg", "JPG"
End With

Worksheets("Combined data").ChartObjects(Worksheets("Combined data").ChartObjects.Count).Delete
UserForm1_MasterSeedOrderForm.Image3.Picture = LoadPicture(Environ$("temp") & "" & "TempChart.jpg")
Kill Environ$("temp") & "" & "TempChart.jpg"
End Sub

 

[Mr_Ragweed2]

Important update!!!! So when i hard code a range it works perfectly and scales to fit the image window. I started with 1 cell and kept experimenting up until the following (just to see what might fit and still be legible):

Call CreateJpg("Combined data", Sheets("Combined data").Range("A1:N20"))

So it must be the "firstrow" "lastrow" that's messing things up. Do i need to Dim the "lastrow"? If so, do i put it in the code before the call or in the called macro?

I'm not very good at that part but is this an option:

Call CreateJpg("Combined data", Sheets("Combined data").Range("A:L" & lastrow))

You know these things way better than i do.

 

[Mr_Ragweed2]

AHHHHHHH!!! It mostly works! It mostly works! (With minor exceptions haha). It does not actually pull the last row, which will always only have data in columns H thru K. Also, it does limit out in length as far as scaling to the image frame goes. My current image window apparently only supports 21 rows.

Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Call CreateJpg("Combined data", Sheets("Combined data").Range("A1:L" & lastrow))

I'm wondering if i just figure out the max size i can make the image window based on everything else on the userform and then just hard code that max number of rows.....
something like this:

Call CreateJpg("Combined data", Sheets("Combined data").Range("A1:L27"))

Even if there would only be 7 rows of data the window would show partially empty anyway. Making the image window size dynamic #1 seems really imposing to me & #2 runs the risk of being too big and hiding other stuff on the userform.

 

[Mr_Ragweed2]

Dave - THANK YOU so much for all of the time you put into this for me!!!! I owe you a beverage . You were very patient with my novice-ness and your replies sparked much thought on my end and proved to be the reason this issue has been resolved.
Again, i'm very grateful for your help.
JW

 

[Mr_Ragweed2]

just to clean up the last error i had - not pulling the actual lastrow. I realized my lastrow statement was looking in column 1 instead of column 11. That fixed the problem.
Was this:

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

now it's this:

lastrow = Cells(Rows.Count, 11).End(xlUp).Row

Again thanks to all who have read this. I hope someone else can benefit from it one day as well.

 

[NdNoviceHlp]

You are welcome. My apologies for the delayed responding as I was away. Glad you were able to sort it. Thanks for posting your outcome. Dave