How to make controls stop moving

I'm using Excel 2007.  I'm saving the file in Compatibility Mode because everyone else has Excel 2003.
I created a Chart Sheet (not a chart on a worksheet) to chart data and put some Form Control combo boxes on it to select the range of data to chart.  There are also some Form Control labels.

When I close the sheet and then open it again, the controls have moved as dispayed in the images below.   The combo boxes get very large and move down the sheet.  How can I get them to stay where I put them or move back to where I put them programatically?
4-12-2010-Buttons1.png
4-12-2010-Buttons2.png
LVL 1
megninAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
I suspect you are running into one of the bugs with charting in 2007. Do you have your Office installation fully patched, and can you create the chart in 2003 instead?
0
yogi4lifeCommented:
Have you tried to right-click the controls and select "Format control" on the pop-up menu - and then selected the Properties tab and checked the "Do not size with chart" ?

If not try that first...

Furthermore you can protect your controls by checking the LOCKED checkbox on the PROTECTION tab in the same dialogbox...
0
Rory ArchibaldCommented:
FYI, one of the bugs fixed in SP2 is:
"A shape's position on a chart is not saved correctly when you save as an .xls file using Excel 2007. The shape appears in a different position when you open the file in Excel 2003."
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

megninAuthor Commented:
Hi yogi4life.  Those were the first things I tried.
I also have service pack 2 installed.  I'm going to check for further updates, but I'm pretty sure I have everything current.

4-12-2010-SP2.png
0
yogi4lifeCommented:
Hmm... I think I will try to make some VBA-code for you later this evening...
0
megninAuthor Commented:
I have SP2 the not up to date with the other updates.  Tthere were 28 Hight Priority updates, 6 of which were for Office 2007 so I'm just installing all of them and see what happens (famous last words ;-).
0
megninAuthor Commented:
oh, yogi, that would be wonderful!  :-)  Thank you!
0
megninAuthor Commented:
I'm afraid that without some method of keeping the controls in place or at least returning them to where they belong using VBA that I would have to scrap the project since it would not be suitable to send to upper management with the controls going all over the place.
Your VBA code solution will be much appreciated.  :-)
0
yogi4lifeCommented:
Here is some code that shows how one Drop Down could be adjusted. It has been put into the Chart sheet's Activate event so it will always be moved to a fixed position. You can extend it by hardcoding the other controls the same way. The code has been put into the VBA projects Chart Sheet.


Private Sub Chart_Activate()
    With Me.Shapes("Drop Down 1")
        .Left = 100
        .Top = 100
        .Height = 16
        .Width = 96
    End With
End Sub

Open in new window

0
megninAuthor Commented:
I can't for the life of me find what the name of the drop down is.
0
megninAuthor Commented:
Never mind.  Apparently one of them is "Drop Down 1".  I left the code as is and one of my drop downs moved to position 100 x 100.  ;-)
0
yogi4lifeCommented:
Actually you could use a number instead to get hold of the Shapes... That is safer in case you delete some shapes and later add some to.... You cant rely on the Shapes being called "Drop Down 1"..."Drop Down ". But Shapes(1) and upwards will never leave any holes...


Private Sub Chart_Activate()
    With Me.Shapes(1)
        .Left = 100
        .Top = 100
        .Height = 16
        .Width = 96
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
Oh, good.  the drop-downs don't really "obey" the .Height setting.  Both do obey the top and width settings.  Is that a glitch or what?
This is the first sub on my Chart sheet:

Private Sub Chart_Activate()
    With Me.Shapes(2)
        .Left = 470
        .Top = 10
        .Height = 20
        .Width = 50
    End With
    With Me.Shapes(1)
        .Left = 550
        .Top = 10
        .Height = 20
        .Width = 50
    End With
End Sub

Open in new window

0
yogi4lifeCommented:
That is because your drop down has "Lock Aspect Ratio" checked ...

If you include this before adjusting the Height and Width on line 5 and 11 like this:

        .LockAspectRatio = msoFalse

It should work as you expect...
0
megninAuthor Commented:
The 'taller' controls seem to accept the hight setting better than the 'shorter' ones.  The drop-downs don't seem to take small adjustments like going from 24 to 12 or 12 to 20, but a "group' of labels is treated like a single control and obeys the height setting okay.
0
megninAuthor Commented:
Ahhhhhhh.  Got ya.  Yeah, I was setting all that stuff while trying to get them to stay in place using the built in settings.  Thanks!
0
megninAuthor Commented:
That was it.  This works great!  
I have several chart sheets with various controls.  I've put the code on two of them.  Using Me.Shapes(n) works great too.  I don't have to worry about what the controls names are.  There are only 2 to 4 controls on a page so identifying which is 1, 2, 3, etc. is no problem.
I think we're done here.  :-)  Any other little tips like the last two life savers for working with these controls?
0
yogi4lifeCommented:
Not that I can think of right now :) and it is getting late here in Sweden -  so I will probably call it a day ...

Good luck
0
megninAuthor Commented:
Oh, yes, of course.  Thank you so much.  I'll close this now.  I really appreciate your help.  This one has been kicking my butt for a long time.
Oh, off topic... I collect WWII rifles and have two very nice Swedish Mausers dated 1899 and 1914 that I'm quite proude of.  ;-)
0
megninAuthor Commented:
Thank you so much.  This worked great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.