• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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
0
megnin
Asked:
megnin
  • 12
  • 6
  • 2
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 12
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now