PDA

View Full Version : Sleeper: Scaling chart based on values



xboon_95
08-05-2014, 03:00 AM
Hi all,

I'm trying to scale my chart according to values. There is no problem trying to plot a graph from 00:00:01 to 00:00:59 with the timing clearly shown, but the problem occurs after 00:01:00. I found out the problem at a later stage, which is that the graph size is not able to expand to fit the x-axis. I went on to try to find codes on auto-scaling of x-axis but to no avail. Any idea on doing this? My codes are shown below :

Select Case ComboBox3
Case "Number of Cycles"
'##### If Chart Does Not Exist Jump to Maker Else Delete It #################
On Error GoTo maker7
ActiveSheet.ChartObjects("First_Chart2").Activate
ActiveChart.Parent.Delete ' Delete Chart If Exists
'##### Make Chart Called My_Chart #################
maker7:
Sheets("sheet1").Activate
Dim SearchRange As Range
Dim FindRow As Range
Set SearchRange = Range("B1", Range("B65536").End(xlUp))
Set FindRow = SearchRange.Find(" 00:00:01", LookIn:=xlValues, LookAt:=xlWhole)
row_review = FindRow.Row
Dim SearchRange1 As Range
Dim FindRow1 As Range
Dim YourValue As Range
Dim TheLastRow As Long
Set SearchRange1 = Range("B1", Range("B65536").End(xlUp))
TheLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim StartVal, endVal
Static SStart, SEnd
Dim Col1
Col1 = "C"
StartVal = Col1 & FindRow.Row
endVal = Col1 & TheLastRow
SStart = "B" & FindRow.Row
SEnd = "B" & TheLastRow
ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.Parent.Name = "First_Chart2" 'Name Chart
ActiveSheet.Shapes("First_Chart2").IncrementLeft 250 'Move Chart
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=ActiveSheet.Range(StartVal & ":" & endVal) ' Series Range
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range(SStart & ":" & SEnd)
ActiveChart.SeriesCollection(1).Name = "=""Number of Cycles Over Time"""
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
'.ForeColor.TintAndShade = 0
'.ForeColor.Brightness = 0.400000006
'.Transparency = 0
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Text = "Number of Cycles"
ActiveChart.ChartTitle.Text = "Number of Cycles Vs Time"
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Text = "Time (hh:mm:ss)"
Dim chartnum As Integer
chartnum = 7
Dim CurrentChart7 As Chart
Dim Fname7 As String
Set CurrentChart7 = Sheets("Sheet1").ChartObjects("First_Chart2").Chart
CurrentChart7.Parent.Width = 400
CurrentChart7.Parent.Height = 250
'Fname1 = ThisWorkbook.Path & "\temp.gif"
Fname7 = Application.DefaultFilePath & Application.PathSeparator & "temp.gif"
CurrentChart7.Export Filename:=Fname7, FilterName:="GIF"
Imagea.Picture = LoadPicture(Fname7)
End With

Bob Phillips
08-05-2014, 06:00 AM
Cross-posted at ExcelGuru http://www.excelguru.ca/forums/showthread.php?3336-Scaling-chart-based-on-values