Is it possible to change chart type based on how many time points are available?

[Rnkhch]

Hello,

I have tables that correspond to 15 timepoints that automatically get updated based on values entered in source tables, and I have xy charts with dots that also automatically adapt to the tables.

Now, certain people in my organization only use the first time point (day 0) and have no need for the remaining time points. Since a bar chart is more suitable for one time point, I'm wondering whether I could use VBA to automatically change the chart type to a bar charts if only one time point appears in my tables? If possible, could someone let me know of the VBA code? This would be a really cool feature to add to my file .

Thanks for any input!

 

[Rnkhch]

Thanks! This one is better. But it seems to be buggy. Like sometimes when I delete a whole column of data, the graph doesn't respond to the deletion. And then it responds on the next round of deletion. But then when it responds it is behind for one point (meaning it is still showing an already deleted column. Also you can get rid of the button as I'm hoping for this to be completely automatic

By the way, I noticed I cannot undo anything in this document, which I assume is the effect of the VBA code? Like when I delete a column, I cannot undo (the undo and redo buttons are completely inactive.)

 

[BSALV]

hello,
with this changed macro, is the "UNDO" solved ? (not if you swap between both types, but when you add a point)

Sub CommandButton1_Click()

     Dim i, iChart, MyMin, MyMax
     Set c = Range("T1Xaxis")     'your X-axis
     MyMin = Application.Min(c)
     MyMax = Application.Max(c)

     For iChart = 1 To ActiveSheet.ChartObjects.Count     'as you have several charts, loop them all
          With ActiveSheet.ChartObjects(iChart).Chart     'your chart

               If (Not bHandshake And .ChartType <> xlColumnClustered) Or (bHandshake And WorksheetFunction.CountA(Range("T1Xaxis")) <= 1) Then    'a cluster when event via Change and number of points <=1 or in toggle-mode

                    If .ChartType <> xlColumnClustered Then .ChartType = xlColumnClustered          'now it's columnclustered
                    With .Axes(xlCategory)     'just for 1 point
                         If .CategoryType <> xlTimeScale Then .CategoryType = xlTimeScale
                         If .MinimumScale <> MyMin Then .MinimumScale = MyMin               'X-value of your first point
                         If .MaximumScale <> MyMin Then .MaximumScale = MyMin
                    End With
  
               Else
                    If .ChartType <> xlXYScatterLines Then .ChartType = xlXYScatterLines         'now it's xyscatter
                    With .Axes(xlCategory)     'more points, how do you want to fix the min and max ??? Do you allow excel to choose or do you ? see 2 options here below
                         If Not .MinimumScaleIsAuto Then .MinimumScaleIsAuto = True
                         If Not .MaximumScaleIsAuto Then .MaximumScaleIsAuto = True
                    End With
                    On Error Resume Next
                      On Error GoTo 0
               End If
          End With
     Next

     bHandshake = False
End Sub

 

[BSALV]

i have to look to this later today ... .
That macro has to be triggered if you change the type of graph, otherwise not, i suppose.

 

[Rnkhch]

Hi,

Yes, that has solved the undo, which is really great! Now if you can make the chart type change automatic, then it will be all set.

Thank you so much!

(Sorry for the delay; was dealing with more jury duty and accident-related stuff)