route217
asked on
Edit macro so vba refer to table as opposed to chart
Hi Experts
How would you edit the following vba so the macro refers to a range of data as opposed to adding a chart
Private Function CreateContainer(ByRef wbk As Workbook) As Chart
Set container = wbk.Charts.Add
With container
.ChartType = xlColumnClustered
.SetSourceData Source:=wbk.Worksheets(1). Range("A1" )
.Location Where:=xlLocationAsObject, Name:=wbk.Sheets(2).Name
End With
Set CreateContainer = ActiveChart
CreateContainer.ChartArea. ClearConte nts
End Function
Sub MakeAndSizeChart(ByRef cht As Chart, ih As Integer, iv As Integer)
Dim Hincrease As Single
Dim Vincrease As Single
Hincrease = ih / cht.ChartArea.Height
cht.Parent.ShapeRange.Scal eHeight Hincrease, _
msoFalse, msoScaleFromTopLeft
Vincrease = iv / cht.ChartArea.Width
cht.Parent.ShapeRange.Scal eWidth Vincrease, _
msoFalse, msoScaleFromTopaleft
End sub
How would you edit the following vba so the macro refers to a range of data as opposed to adding a chart
Private Function CreateContainer(ByRef wbk As Workbook) As Chart
Set container = wbk.Charts.Add
With container
.ChartType = xlColumnClustered
.SetSourceData Source:=wbk.Worksheets(1).
.Location Where:=xlLocationAsObject,
End With
Set CreateContainer = ActiveChart
CreateContainer.ChartArea.
End Function
Sub MakeAndSizeChart(ByRef cht As Chart, ih As Integer, iv As Integer)
Dim Hincrease As Single
Dim Vincrease As Single
Hincrease = ih / cht.ChartArea.Height
cht.Parent.ShapeRange.Scal
msoFalse, msoScaleFromTopLeft
Vincrease = iv / cht.ChartArea.Width
cht.Parent.ShapeRange.Scal
msoFalse, msoScaleFromTopaleft
End sub
ASKER
Sl8rz thanks for the feedback
Assume named range was table 1 how would u complete the steps...
Assume named range was table 1 how would u complete the steps...
ASKER
Sl8rz
Apologies for asking but how would u chase the vba code..
Apologies for asking but how would u chase the vba code..
Start with this:
http://www.youtube.com/watch?v=YCPGe3-ESKg
Then read this:
http://www.mrexcel.com/forum/excel-questions/26055-how-refer-named-ranges-column-numbers-visual-basic-applications.html
Let me know if this is enough direction or not.
http://www.youtube.com/watch?v=YCPGe3-ESKg
Then read this:
http://www.mrexcel.com/forum/excel-questions/26055-how-refer-named-ranges-column-numbers-visual-basic-applications.html
Let me know if this is enough direction or not.
ASKER
Hi Sl8rz
It not the direction I have no vba programming experience..
It not the direction I have no vba programming experience..
Can you tell me a bit more about what you want to accomplish exactly. Your title says "refer to a table" however, your post says "refer to a range." Do you even want a chart at all?
Just need some background please.
Just need some background please.
ASKER
Apologies
What I want is the macro to copy a range of data in and paste this range into the email body as a jpg image - the full macro code is here https://www.experts-exchange.com/questions/27998830/Jpg-image-not-visible-once-macro-has-run.html
I though that if I broke the question down it would be much easier to answer..
I do not want the Chart at all...
What I want is the macro to copy a range of data in and paste this range into the email body as a jpg image - the full macro code is here https://www.experts-exchange.com/questions/27998830/Jpg-image-not-visible-once-macro-has-run.html
I though that if I broke the question down it would be much easier to answer..
I do not want the Chart at all...
ASKER
Sl8rz
Thanks for the feedback... But I cannot send screen shots to senior people...
Thanks for the feedback... But I cannot send screen shots to senior people...
You said you wanted to send a jpeg of the worksheet, right? When you send a screenshot you'll be sending a jpeg (or other image format) of the worksheet. What is the difference?
ASKER
sl8rz
You said you wanted to send a jpeg of the worksheet, right? - Sorry - just a range of data in the worksheet....not the whole worksheet...
You said you wanted to send a jpeg of the worksheet, right? - Sorry - just a range of data in the worksheet....not the whole worksheet...
If you notice, the link that I'm suggesting shows you how to grab only the part of the worksheet that you want (it could be a tiny block of just a few cells) then puts that in the body of an email.
ASKER
My other question looks at the get boiler room function for the HTML text section and also inform the user if the have set up email signature so how do I incorporate into the other code...
I understand what u are saying...
I understand what u are saying...
So is that solution going to work for you? Let's establish that before we dig deeper.
ASKER
Yep...
Ok. Now what's this about boiler room functions and signatures?
ASKER
Just asking if we need these... Nothing else
I can't really speak to what your needs are, but of course if you wish to have a signature check you can. As far as any boilerplate code (I suppose that is what you meant) no, I don't see any need for any extra HTML (not after you've changed the title and filled in the actual email addresses).
ASKER
Question:- if the original code refer to a chart why can't we change to ref to a range of data in an excel sheet....
And use that or are u about to post the solution...
And use that or are u about to post the solution...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://excel.tips.net/T003106_Using_Named_Ranges_in_a_Macro.html
and
http://kjellsj.blogspot.com/2006/08/using-excel-to-generate-picklist-xml.html