Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

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

  1. #1
    Join Date
    29th September 2011

    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


    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    25th April 2013

    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.

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    29th September 2011

    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...

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: June 11th, 2013, 21:57
  2. Trying to launch macro utilities in Excel 2013 Preview
    By chris verbeski in forum HAV-A-CHAT
    Replies: 3
    Last Post: January 19th, 2013, 18:55
  3. Turning data from 1 column into 5 columns
    By timvb85 in forum EXCEL HELP
    Replies: 6
    Last Post: February 10th, 2011, 07:37
  4. Replies: 4
    Last Post: December 12th, 2003, 02:52


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts