View Full Version : chart with vba
skaswani
08-26-2007, 07:29 AM
actually, I have a question regarding Charts with Vba
what I want to do
1) Loop all sheets (almost 100 sheets)
2) in Loop I want to create CHARTS
3) problem is that all sheets have different number of rows!
I.e, some sheets have 100 records, some have 500 & some have more!
but the data is of 2 years
so, is this possible to make such code in VBA
I have made a code, but,
actually it doesn't allow me to RE-Create Charts because it allocate a Name to that chart which is static,
2nd, the same it does with Sheet name (Data Source) it actually use the static name as I mention above
please kindly help me out
I am pasting the code here,
my Charts are Base on DATE & Closing RATES
regards,
Sub MakeChart()
Dim Current As Worksheet
For Each Current In Worksheets
Sheets(Current.Index).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("AL-Habib").Range("A3:B360"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="AL-Habib"
ActiveChart.HasDataTable = False
ActiveWindow.Visible = False
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Range("F6").Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Paste
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft 156#
ActiveSheet.Shapes("Chart 3").IncrementTop -38.25
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
Next
End Sub
mdmackillop
08-26-2007, 07:46 AM
Hi Skaswani
Welcome to VBAX
Can you post a small sample (two or three sheets) . Use Manage Attachments in the Go Advanced section.
Regards
MD
skaswani
08-26-2007, 08:10 AM
hello, i dont know where i can find that option:(
please kindly give me the direct link or can i email u??
regards,
skaswani
08-26-2007, 08:13 AM
here it is:)
thanks,
geekgirlau
08-26-2007, 07:00 PM
Sub MakeChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart
For Each wks In ActiveWorkbook.Worksheets
Set rng = wks.Range("A2:B" & wks.UsedRange.Rows.Count)
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)
With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy:=xlColumns
On Error Resume Next
.HasDataTable = False
.HasLegend = False
.Axes(xlCategory).TickLabels.NumberFormat = "[$-409]mmm-yy;@"
With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 300
End With
End With
Next wks
End Sub
skaswani
08-27-2007, 01:09 PM
hey GeekGirl
how are you
thankyou so much for this code!
i have modified it as per my need!
the only one problem which i am facing in the end that
- i want to loop till CELL/Column "A" is empty
the loop code which you gave is not working on my sheet, bec my sheet have some IF & other Forumla's even if the Column A is empty
so, is this possible to Loop till Cell A is NOT EMPTY
btw, Cell A have Dates
here is my Code!
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart
Sub mkChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart
For Each wks In ActiveWorkbook.Worksheets
Set rng = wks.Range("A3:B360")
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)
With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy:=xlColumns
On Error Resume Next
.HasDataTable = False
.HasLegend = False
.Axes(xlCategory).TickLabels.NumberFormat = "[$-409]mmm-yy;@"
With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 350
End With
End With
Call change_unit
Next wks
MsgBox "Done"
UserForm1.Hide
End Sub
Sub change_unit()
For Each wks In ActiveWorkbook.Worksheets
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.PlotArea.Select
Selection.ClearFormats
Next wks
End Sub
Edited 28-Aug-07 by geekgirlau. Reason: insert VBA tags
geekgirlau
08-27-2007, 05:17 PM
Okay, try this:
Sub mkChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart
For Each wks In ActiveWorkbook.Worksheets
Set rng = wks.Range(wks.Range("A1"), wks.Range("A" & Cells.Rows.Count).End(xlUp))
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)
With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy:=xlColumns
On Error Resume Next
.HasDataTable = False
.HasLegend = False
With .Axes(xlCategory)
.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
.PlotArea.ClearFormats
With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 350
End With
End With
Next wks
MsgBox "Done"
End Sub
A couple of things to note:
Please use the vba tags when posting code - I've edited your post above for you. When you paste the code into the thread, select the text then click on the "VBA" button.
I've removed your "change_unit" procedure and incorporated it into the "mkChart" procedure. Your method creates too many loops. The first procedure loops through every worksheet, and calls the second procedure that also loops through every worksheet. As the second procedure is just making further modifications to the same chart, it's much easier to just add those additional changes in the first pass.
skaswani
08-27-2007, 11:10 PM
Dear Mam,
Sorry, actuly i am new here in this Forum
thank you very much for helping me again & again,.
but i think you hav'nt check my WorkBook
let me explain you
1) for charts ,my data is starting from A 3 Cell, not with A 1
2) i have to use 2 columns, that are A & B
A have the Dates & B have Closing Rates
but when i run your code, it
start from Row A 1
it doesnot include Row B
sorry for trouble,
regards,
geekgirlau
08-27-2007, 11:34 PM
Change this line:
Set rng = wks.Range(wks.Range("A1"), wks.Range("A" & Cells.Rows.Count).End(xlUp))
to this:
Set rng = wks.Range(wks.Range("A3"), wks.Range("A" & Cells.Rows.Count).End(xlUp).Offset(0,1))
skaswani
08-27-2007, 11:42 PM
oops,
do u know what
the data source which it is using is the 1st Sheet :s
:s, any idea
geekgirlau
08-27-2007, 11:53 PM
I don't understand the question ... the data source for each chart is the range on the current sheet
skaswani
08-28-2007, 12:06 AM
yes, i also believe that is the right way, but when i check the data source of the CHART it take me to SHEET1
let me upload the file,
if you right click on the CHART for check the Source you will see this problem
regards,
geekgirlau
08-28-2007, 12:19 AM
Sub mkChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart
For Each wks In ActiveWorkbook.Worksheets
wks.Range("A1").Select
Set rng = wks.Range(wks.Range("A3"), wks.Range("A" & wks.Cells.Rows.Count) _
.End(xlUp).Offset(0, 1))
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)
With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=wks.Range(rng.Address), PlotBy:=xlColumns
On Error Resume Next
.HasDataTable = False
.HasLegend = False
With .Axes(xlCategory)
.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
.PlotArea.ClearFormats
With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 350
End With
End With
Next wks
MsgBox "Done"
End Sub
skaswani
08-28-2007, 12:29 AM
Thank You SO MUCH!
GOD BLESS YOU! ameen
lastly io want to ask, can you please recommend me any EBook for VBA!
i really want to learn it,
regards,!
geekgirlau
08-28-2007, 12:33 AM
I don't know of any personally, but you might want to search the site or post a new question to get some recommendations.
skaswani
08-28-2007, 12:51 AM
can i add a title to chart??
i try to add
like this
.ChartTitle.Characters.Text = wks.Name
any idea!
geekgirlau
08-28-2007, 05:54 PM
Almost ...
With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=wks.Range(rng.Address), PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = wks.Name
skaswani
08-29-2007, 09:57 AM
thanks alot, i'll check it tomorow morning,
thanks again!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.