[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Add chart to active sheet with no name

Posted on 2002-04-05
4
Medium Priority
?
172 Views
Last Modified: 2012-08-13
I want to write a macro to add charts to  a number of worksheets.  The code produced when I record a macro graphs the data from  the worksheet where I recorded it (e.g., "Sheet1").   How can I do this so the chart is placed on the ACTIVE worksheet?

0
Comment
Question by:suobs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Expert Comment

by:macbone2
ID: 6922203
The code below will place a chart in each worksheet in the book. I'm presuming your data is in columns a & b in each sheet. Try placing the data below in cols A & B, then copy and paste the code below into a VBA macro, then run it or step thru it.

Sub charter()
Dim sheetname1 As String, shtscnt As Long, rowscount As Long
Dim SelRange As String, SSRange As String
For shtscnt = 1 To Sheets.Count

sheetname1 = Sheets(shtscnt).Name
Worksheets(sheetname1).Activate
rowscount = Range("A1").CurrentRegion.Rows.Count
SelRange = "A1:A" & rowscount & ", B1:B" & rowscount
SSRange = "A1:B" & rowscount
 '''replaced by string value   Range("A1:A9,B1:B9").Select
    Range(SelRange).Select
    Charts.Add
    ActiveChart.ChartType = xlLineStacked
    ActiveChart.SetSourceData Source:=Sheets(sheetname1).Range(SSRange), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sheetname1
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Roulette (phewey)"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    Next shtscnt
End Sub

The range in column a would be Times and col B values
eg
Time
(mins) Money Lost
20     10
40     30
60     50
80     60
100     50
120     90
140     120
160     200
0
 
LVL 44

Accepted Solution

by:
bruintje earned 400 total points
ID: 6922365
Hi Suobs,

-the following code will
-use the active sheet
-as a source
-as a place

Public Sub ChartCustomRange()
Dim strSheetName As String
Dim rngChartRange As String
  strSheetName = ActiveSheet.Name
  rngChartRange = InputBox("Give range to chart", "Chartrange", "A10:D25")
  Charts.Add
  ActiveChart.ChartType = xlColumnClustered
  ActiveChart.SetSourceData Source:=Sheets(strSheetName).Range(rngChartRange), _
    PlotBy:=xlColumns
  ActiveChart.Location Where:=xlLocationAsObject, _
    Name:=strSheetName
End Sub

-for different chart types you can play around with this line
  ActiveChart.ChartType = xlColumnClustered
-by using intellisense on the part
  ActiveChart.ChartType = xl[click CTRL+TAB here]

-a good resource on charting and coding in excel
http://www.geocities.com/jonpeltier/Excel/Charts/

HTH:O)Bruintje
0
 

Author Comment

by:suobs
ID: 6923675
Macbone2's macro is very cool, but works on multiple pages. Bruintje's works on individual pages, exactly what I was looking for, so gets the points.
 
Thanks both of you!
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6923681
thanks for the grade and the points
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
New style of hardware planning for Microsoft Exchange server.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 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