Click here to Skip to main content
12,998,277 members (62,056 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Sheets(appServer & "chart").Select
ActiveChart.ChartTitle.Text = appServer
Selection.Width = 434.944
Selection.Left = 466.86
Selection.Top = 62.544

This is my code to shrink the plot area of a chart in order to move the legend over to make room for checkboxes to toggle the display of each series. The program works great until I save and reopen it. When it is reopened the checkboxes end up on top of the legend and I can easily fix that every time I reopen the file, but I would rather just fix the issue in my code.Thanks in advance for any help.

Dim chartRangeStart As Integer
Dim chartRangeEnd As Integer
Dim serverNum As Integer
Dim numOfServers As Integer
Dim topOfCheckbox As Integer
Dim appServerNum As Integer
Dim appServer As String
chartRangeStart = 51
chartRangeEnd = 98
numOfServers = 23
serverNum = 1
topOfCheckbox = 56
appServerNum = 0
appServer = server
   Do While serverNum <= numOfServers
' adds a checkbox for each appServer in an array
    ActiveChart.CheckBoxes.Add(4529766, 485607, 685185.75, 685896.75).Select
    Selection.Characters.Text = ServerNamesArray(appServerNum)
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoFalse
      With Selection
        .Value = xlOff
        .LinkedCell = appServer & "!$" & ColumnArray(appServerNum) & "$2"
        .Display3DShading = False
      End With
' moves the checkbox to where I want it (until I save and reopen)
    Selection.ShapeRange.IncrementLeft 465#
    Selection.ShapeRange.IncrementTop topOfCheckbox
    Selection.ShapeRange.ScaleWidth 2#, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1#, msoFalse, msoScaleFromTopLeft
    topOfCheckbox = topOfCheckbox + 13

This is the code that adds the checkboxes to the chart.
Posted 31-Jul-12 2:03am
Updated 31-Jul-12 2:23am
Kenneth Haugland 31-Jul-12 8:06am
Make the changes and save the excel file? ;) Im having a problem as I cant see that you set your checkboxes anywhere...
Smithk32 31-Jul-12 8:21am
haha I tried that several times, each time I rerun my macro to create the chart and add the checkboxes it looks perfect, but as soon as I save it, close and then reopen, all of the checkboxes appear on top of the legend
Kenneth Haugland 31-Jul-12 8:31am
Im not sure I could help you, can you make the simples example you could think of (in code and post that instead of all the fanzy stuff you are trying to do? Th(Im saying the only way I could help you is by trail and error, and im a little too lazy to do all this myself ;) )
Smithk32 31-Jul-12 8:38am
Theres not really anyway I can shrink the code to make it easier, almost every peice of the code only creates or does one thing as is just repeated in a loop. Thanks for your help though.
ChandraRam 31-Jul-12 8:50am
Can you place the code to re-size your chart in the Workbook_open event? Crude, but it will at least work :)
Smithk32 31-Jul-12 8:58am
I can try :)
Smithk32 1-Aug-12 12:27pm
That would have worked, but I need the checkboxes to only move once, so I wrote another sub procedure to move the checkboxes and button over. For some reason once that sub is run and I save and close and reopen the chart is just the way I saved it, so I don't know why it wouldn't be that way from the first time but o well it works. Thank you everyone that tried to help.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I know this isnt exactly what you are looking for, but a designated forum for VSTO programming in Excel could be found here. I have at least been given answers there that I didnt get anywere else. Its worth a try anyway :)
Smithk32 1-Aug-12 14:51pm
Thanks, I am thinking it is an issue within excel 2007 because once I move all of the shapes back to where they should be it doesn't happen again.
Kenneth Haugland 1-Aug-12 14:53pm
I think the error should be registered at Microsoft Connect witch is their bug database :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I figured out that it is most likely an issue within Microsoft Excel 2007 itself because once I move all of the shapes back to where they should be I don't have any more issues. Thank you everyone that tried to help.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170622.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100