Excel chart variable X-axis

[JBPNTRN]

Hi,
My question is in twofold and related to eachother:
* I have plot chart linked to a Table with the dates on the X-axis of the chart. Depending on the month selected for the table (dropdown), the number of days differs of course.
The chart however than needs to be adjusted every time I select a different month. For example for Feb the X-axis goes until 28/02 and then has 2-3 open spots as they fall within the X-axis range. How can I make the range of the x-axis variable / adjust to the number of days of the month selected?
* Related to this: When I select the current month, the future days have no data avaiable yet. In the graph, the line drops for these days to zero. Is it possible to ignore the days without any data. For example, the graph stops at the last data point.
Probably the second can be solved by solving the first question.

Your help is appreciated

 

[Worf]

Welcome to the Board

• Here is a VBA solution. Run the Initialize App Events routine to enable events.
• I am assuming you are using a real table as source data. When the table is filtered, the chart’s axis should adjust automatically.
• Tell me if you need my test workbook. Alternatively, you can post a link to your workbook.

' class module named CChartEvent
Public WithEvents EventChart As Chart
 
Private Sub EventChart_Activate()
MsgBox "activate"                   ' for testing, can be deleted.
End Sub


Private Sub EventChart_Calculate()
Dim t As ListObject, r As Range, ch As ChartObject, a As Axis
Set t = ActiveSheet.ListObjects("Table1")
Set r = t.DataBodyRange.SpecialCells(xlCellTypeVisible)
If r.Areas.Count > 1 Then
    MsgBox "Only one area expected, aborting..."
    Exit Sub
End If
Set ch = Me.EventChart.Parent
ch.Chart.DisplayBlanksAs = xlNotPlotted                         ' future days
Set a = ch.Chart.Axes(xlCategory)
a.MinimumScale = WorksheetFunction.Min(r.Columns(2))
a.MaximumScale = WorksheetFunction.Max(r.Columns(2))
End Sub

' based on code by Jon Peltier / place on standard module
Dim clsAppEvent As New CAppEvent, clsChartEvent As New CChartEvent, clsChartEvents() As New CChartEvent


Sub InitializeAppEvents()
  Set clsAppEvent.EventApp = Application
  Set_All_Charts
End Sub
 
Sub TerminateAppEvents()
  Set clsAppEvent.EventApp = Nothing
  Reset_All_Charts
End Sub


Sub Set_All_Charts()
Dim chtobj As ChartObject, chtnum%
' Enable events for active sheet if sheet is a chart sheet
If TypeName(ActiveSheet) = "Chart" Then Set clsChartEvent.EventChart = ActiveSheet
 ' Enable events for all charts embedded on a sheet
' Works for embedded charts on a worksheet or chart sheet
If ActiveSheet.ChartObjects.Count > 0 Then
    ReDim clsChartEvents(1 To ActiveSheet.ChartObjects.Count)
     chtnum = 1
    For Each chtobj In ActiveSheet.ChartObjects
        Set clsChartEvents(chtnum).EventChart = chtobj.Chart
        chtnum = chtnum + 1
    Next
End If
End Sub
 
Sub Reset_All_Charts()    ' Disable events for all charts previously enabled together
Dim chtnum%
On Error Resume Next
Set clsChartEvent.EventChart = Nothing
For chtnum = 1 To UBound(clsChartEvents)
    Set clsChartEvents(chtnum).EventChart = Nothing
Next
End Sub