Solved

Edit macro so vba refer to table as opposed to chart

Posted on 2013-01-17
20
353 Views
Last Modified: 2013-01-17
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.ClearContents
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.ScaleHeight Hincrease, _
                                     msoFalse, msoScaleFromTopLeft
   Vincrease = iv / cht.ChartArea.Width
   cht.Parent.ShapeRange.ScaleWidth Vincrease, _
                                     msoFalse, msoScaleFromTopaleft

End sub
0
Comment
Question by:route217
  • 10
  • 10
20 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38787858
0
 

Author Comment

by:route217
ID: 38787923
Sl8rz thanks for the feedback

Assume named range was table 1 how would u complete the steps...
0
 

Author Comment

by:route217
ID: 38788223
Sl8rz

Apologies for asking but how would u chase the vba code..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38788265
0
 

Author Comment

by:route217
ID: 38788271
Hi Sl8rz

It not the direction I have no vba programming experience..
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38788330
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.
0
 

Author Comment

by:route217
ID: 38788374
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 http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27998830.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...
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38788559
0
 

Author Comment

by:route217
ID: 38788804
Sl8rz

Thanks for the feedback... But I cannot send screen shots to senior people...
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38788839
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?
0
 

Author Comment

by:route217
ID: 38788936
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...
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38789009
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.
0
 

Author Comment

by:route217
ID: 38789051
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...
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38789071
So is that solution going to work for you?  Let's establish that before we dig deeper.
0
 

Author Comment

by:route217
ID: 38789102
Yep...
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38789403
Ok.  Now what's this about boiler room functions and signatures?
0
 

Author Comment

by:route217
ID: 38789413
Just asking if we need these... Nothing else
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38789486
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).
0
 

Author Comment

by:route217
ID: 38789540
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...
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 38789593
Just use what they have.  The chart is the vehicle used to capture the image.  It isn't a chart per se, however, the object that displays the image is (a chart object that is).  So don't worry about seeing "chart" code in there...it's all good.  

As to the range of data your are referring to, yes, you need to put that in.  Look at line 6 in the example ("A1:P37") is the range.  You just need to enter the range that works for your situation.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilā€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question