No announcement yet.

Excel 2013 '400 error' turning data labels on with vba

  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel 2013 '400 error' turning data labels on with vba

    Hi All

    I have been using the following code to toggle some chart data labels on and off using a macro - the data labels show price and date data at certain points on a stock chart. The idea was you can show the price and date of BUY and SELL trades, or de-clutter the chart and turn them off.

    Now this worked just fine on Excel 2010 and Excel 2007, but when running on Excel 2013 it throws up an error '400' in a message box.

    Sub ToggleDataLabels()
        Worksheets("Charts").Unprotect Password:="unlock"
        Dim lngSeries As Long
        Dim xx As DataLabels
        With ActiveSheet.ChartObjects(1).Chart
            For lngSeries = 1 To 3
                With .SeriesCollection(lngSeries)
                    .HasDataLabels = Not .HasDataLabels
                    If .HasDataLabels Then
                        With .DataLabels
                            .ShowCategoryName = True
                            .ShowValue = True
                            .ShowSeriesName = True
                        End With
                    End If
                End With
        End With
        Worksheets("Charts").Protect Password:="relock"
    End Sub
    Any ideas as to where I am going wrong? I noticed some odd differences with Office 2013, code that previously worked in 2010 didn't work in Office 2013, another one being cut and paste operations.

    Many thanks in advance for your help!

    Best Regards


  • #2
    Re: Excel 2013 '400 error' turning data labels on with vba

    If I had to wager a guess, it is with your protection. The algorithm used for protection and unprotection is different in Excel 2013 and caused all of my Excel applications that worked previously in 2007 and 2010 to throw strange errors when using password protection in 2013. The workaround for me was to rearrange or limit the times I protect and unprotect worksheets. Try protecting/unprotecting without a password and see if that is in fact the issue.

    I have no idea if that would help, but it is worth mentioning all the same.


    • #3
      Re: Excel 2013 '400 error' turning data labels on with vba

      Well, your wager seemed pretty accurate! It turned out to be a novice error - the password I had used in the vba was different to the one I had used to manually lock the worksheet when I had finished editing. Once I re-locked the worksheet using the same password as that in the vba, it worked fine!

      I am still confused however as to how it worked using Office 2010 and Office 2007...

      Still, it works! Many thanks...