In my Excel chart, the Xaxis shows currently sequential integers represent seconds in multiples of 10, as follows: 10 20 30 40 50 60 70 80 90 100 and so on. How to show in this Xaxis minutes and seconds like this: 00:00 00:10 00:20 00:30 00:40 00:50 01:00 01:10 01:20 01:30 and so on? I use the VBA below to generate the chart.
Sub NewChart()
'Create a chart
Sheets(1).Select 'Select the active Sheet
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
LastLine = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
Dim MaxScale As Integer
Dim MTotal As Integer
Dim Aprox As Integer
Dim vName As String
vName = Sheets(2).Range("B3")
Dim Qx As Integer
Qx = LastLine - 1
Dim Rangg As Integer
Rangg = (LastLine * 10) - 60
MTotal = Rangg
MaxScale = Rangg + 20
With ActiveChart
.ChartType = xlXYScatter
'Set data source range.
.SetSourceData Source:=Sheets(2).Range("A5:A" & Qx & ",B5:B" & Qx & ",E5:E" & Qx & ",H5:H" & Qx & ", K5:K" & Qx & ", N5:N" & Qx & ", Q5:Q" & Qx) ', 'PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = vName '"2-7μm"
'The Parent property is used to set properties of the Chart.
With .Parent
.Top = 2
.Left = 2
.Width = 540
.Height = 252
.Name = "2micron"
End With
ActiveChart.Legend.Select
With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Tahoma"
.NameFarEast = "Tahoma"
.Name = "Tahoma"
End With
.Axes(xlCategory).MajorTickMark = xlInside
.Axes(xlCategory).MinorTickMark = xlInside
.Axes(xlCategory, xlPrimary).Select
.Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 5
.Axes(xlCategory, xlPrimary).TickLabels.Font.Name = "Tahoma"
.Axes(xlCategory, xlPrimary).TickLabels.Font.Bold = msoTrue
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (seconds)"
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 11
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Bold = msoTrue
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Name = "Tahoma"
.Axes(xlCategory, xlPrimary).MinorUnitIsAuto = False
.Axes(xlCategory, xlPrimary).MajorUnit = 300
.Axes(xlCategory, xlPrimary).MinorUnit = 60
.Axes(xlCategory, xlPrimary).MaximumScale = MaxScale
.Axes(xlCategory, xlPrimary).MinimumScale = 0
.Axes(xlCategory, xlPrimary).HasMajorGridlines = True
.Axes(xlCategory, xlPrimary).HasMinorGridlines = True
.Axes(xlCategory).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
.Axes(xlValue).MajorTickMark = xlInside
.Axes(xlValue).MinorTickMark = xlInside
.Axes(xlValue, xlPrimary).Select
.Axes(xlValue, xlPrimary).HasMajorGridlines = True
.Axes(xlValue, xlPrimary).HasMinorGridlines = True
.Axes(xlValue, xlPrimary).TickLabels.Font.Size = 11
.Axes(xlValue, xlPrimary).TickLabels.Font.Name = "Tahoma"
.Axes(xlValue, xlPrimary).TickLabels.Font.Bold = msoTrue
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y axis Legend"
.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 11
.Axes(xlValue, xlPrimary).AxisTitle.Font.Name = "Tahoma"
.Axes(xlValue, xlPrimary).AxisTitle.Font.Bold = msoTrue
.Axes(xlValue).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
.Legend.IncludeInLayout = False
.Legend.Select
Selection.Position = xlTop
Selection.Font.Size = 11
Selection.Font.Name = "Tahoma"
Selection.Font.Bold = msoTrue
ActiveSheet.Shapes("2micron").ScaleWidth 1, msoFalse, _
msoScaleFromTopLeft
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Select
Selection.Left = 2
Selection.Top = 2
Selection.Format.TextFrame2.TextRange.Font.Size = 13.2
Selection.Format.TextFrame2.TextRange.Font.Name = "Tahoma"
Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
ActiveChart.Legend.Select
Selection.Left = 180
Selection.Top = 2
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
End With
ActiveChart.PlotArea.Select
Selection.Top = 22
Selection.Left = 20
Selection.Height = 207
Selection.Width = 540
Selection.Border.LineStyle = xlSolid
Selection.Border.Color = vbBlack
Selection.Interior.Color = vbWhite
End With
Call f_l2m1(1, 8, RGB(0, 176, 240))
Call f_l2m1(2, 3, RGB(255, 0, 0))
Call f_l2m1(3, 1, RGB(255, 0, 255))
Call f_l2m1(4, 2, RGB(153, 0, 255))
Call f_l2m1(5, 4, RGB(153, 0, 255))
Call f_l2m1(6, 9, RGB(146, 208, 80))
Range("a22").Select
End Sub
Sub f_l2m1(LineNo, MStyle, vRGB)
With ActiveSheet.ChartObjects("2micron").Chart
ActiveChart.SeriesCollection(LineNo).Select
With Selection
.MarkerStyle = MStyle
.MarkerSize = 7
.MarkerForegroundColor = vRGB
End With
Selection.Format.Fill.Visible = msoFalse
Selection.Format.Line.Visible = msoFalse
Selection.Format.Line.ForeColor.RGB = vRGB
End With
End Sub
