Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB Excel
   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.
 
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 31-Jul-12 2:03am
Edited 31-Jul-12 2:23am
v3
Comments
Kenneth Haugland at 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 at 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 at 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 at 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 at 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 at 31-Jul-12 8:58am
   
I can try :)
Smithk32 at 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
good
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 Smile | :)
  Permalink  
Comments
Smithk32 at 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 at 1-Aug-12 14:53pm
   
I think the error should be registered at Microsoft Connect witch is their bug database :)
Rate this: bad
good
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.
  Permalink  

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

  Print Answers RSS
0 Dnyaneshwar@Pune 604
1 OriginalGriff 394
2 thatraja 370
3 Sergey Alexandrovich Kryukov 329
4 CPallini 179
0 OriginalGriff 697
1 Dnyaneshwar@Pune 604
2 Kornfeld Eliyahu Peter 495
3 thatraja 370
4 Sergey Alexandrovich Kryukov 344


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2014
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