xboon_95
10-12-2014, 08:23 PM
Hi all, I'm trying to create a chart based on the data(large amount of data) and display it on the image user form control. The problem I'm facing now is that it is only to display 3/4 of my X-axis timing. The chart is able to display the entire length of data but only if i expand it. I came across something called auto-scaling but I'm not sure how to apply it in my situation. My codes are shown below:
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 Rng As Range, Dn As Range
Dim Lstrow As Long
Set Rng = Range("B65536", Range("B65536").End(xlUp))
For Each Dn In Rng
If Dn = Dn.Offset(1) Then
Lstrow = Dn.Row
Exit For
End If
Next Dn
Dim StartVal, endVal
Static SStart, SEnd
Dim Col1
Col1 = "C"
StartVal = Col1 & FindRow.Row
endVal = Col1 & Lstrow
SStart = "B" & FindRow.Row
SEnd = "B" & Lstrow
ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.Parent.Name = "First_Chart1" 'Name Chart
ActiveSheet.Shapes("First_Chart1").IncrementLeft 288 'Move Chart
ActiveChart.ChartType = xlLine
ActiveChart.Legend.Delete
ActiveChart.PlotArea.Select
With Selection.Interior
.ColorIndex = 15
.PatternColorIndex = 5
.Pattern = xlSolid
ActiveChart.ChartArea.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 5
.Pattern = xlSolid
ActiveChart.SetSourceData Source:=ActiveSheet.Range(StartVal & ":" & endVal), _
PlotBy:=xlColumns ' 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
.Weight = 2
'.ForeColor.TintAndShade = 0
'.ForeColor.Brightness = 0.435000006
'.Transparency = 1
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 = ActiveSheet.ChartObjects("First_Chart1").Chart
CurrentChart7.Parent.Width = 430
CurrentChart7.Parent.Height = 270
Fname7 = ThisWorkbook.Path & "\total_cycle.gif"
CurrentChart7.Export Filename:=Fname7, FilterName:="GIF"
Imagea.Picture = LoadPicture(Fname7)
ActiveSheet.ChartObjects("First_Chart1").Activate
ActiveChart.Parent.Delete ' Delete Chart If Exists
End With
End With
End With
StartVal and endVal are the minimum and maximum X-axis values respectively.
Any help is appreciated, thank you (:
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 Rng As Range, Dn As Range
Dim Lstrow As Long
Set Rng = Range("B65536", Range("B65536").End(xlUp))
For Each Dn In Rng
If Dn = Dn.Offset(1) Then
Lstrow = Dn.Row
Exit For
End If
Next Dn
Dim StartVal, endVal
Static SStart, SEnd
Dim Col1
Col1 = "C"
StartVal = Col1 & FindRow.Row
endVal = Col1 & Lstrow
SStart = "B" & FindRow.Row
SEnd = "B" & Lstrow
ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.Parent.Name = "First_Chart1" 'Name Chart
ActiveSheet.Shapes("First_Chart1").IncrementLeft 288 'Move Chart
ActiveChart.ChartType = xlLine
ActiveChart.Legend.Delete
ActiveChart.PlotArea.Select
With Selection.Interior
.ColorIndex = 15
.PatternColorIndex = 5
.Pattern = xlSolid
ActiveChart.ChartArea.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 5
.Pattern = xlSolid
ActiveChart.SetSourceData Source:=ActiveSheet.Range(StartVal & ":" & endVal), _
PlotBy:=xlColumns ' 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
.Weight = 2
'.ForeColor.TintAndShade = 0
'.ForeColor.Brightness = 0.435000006
'.Transparency = 1
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 = ActiveSheet.ChartObjects("First_Chart1").Chart
CurrentChart7.Parent.Width = 430
CurrentChart7.Parent.Height = 270
Fname7 = ThisWorkbook.Path & "\total_cycle.gif"
CurrentChart7.Export Filename:=Fname7, FilterName:="GIF"
Imagea.Picture = LoadPicture(Fname7)
ActiveSheet.ChartObjects("First_Chart1").Activate
ActiveChart.Parent.Delete ' Delete Chart If Exists
End With
End With
End With
StartVal and endVal are the minimum and maximum X-axis values respectively.
Any help is appreciated, thank you (: