Click here to Skip to main content
15,886,578 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
VB
Sheets(appServer & "chart").Select
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = appServer
ActiveChart.PlotArea.Select
Selection.Width = 434.944
ActiveChart.Legend.Select
ActiveChart.ChartArea.Select
ActiveChart.Legend.Select
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.

VB
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.Solid
    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
     
    Loop


This is the code that adds the checkboxes to the chart.
Posted
Updated 31-Jul-12 2:23am
v3
Comments
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 :)

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.
 
Share this answer
 
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 :)
 
Share this answer
 
Comments
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 :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900