Weird button moving problem in Excel 1

[SkydivingSteve]

I have a small Excel spreadsheet with some buttons with code attached located in Row 3.

This was all working fine but recently a problem has occurred.

When user prints the sheet the buttons move unpredictably but usually all wind up on top of each other so that only one shows. This happens no matter how the user prints (Ctrl-P, File Print, Toolbar etc).

I guess that this has something to do with a recent upgrade to the network but I've no evidence for that belief. Running on Win 2000 pro and Excel 2002 (SP3).

Any ideas?

Steve
"In the jungle of the senses

 

[KenWright]

It's a documented bug:-

http://support.microsoft.com/default.aspx?scid=kb;EN-US;838910

Controls move to the left of the worksheet in Microsoft Excel 2002

But Jim Rech recently posted this in the MS newsgroups:

This article is now obsolete. Since the 10/12/2004 security patch there is no need to get a hotfix (although this article does not directly mention this fix, it's in there).

http://support.microsoft.com/default.aspx?scid=kb;en-us;832332

But there have been a number of reports in the MS groups posted that it hasn't seemed to work for all workbooks, and that another option was to use the workaround and change the properties of each of the objects to move and size with cells:-

1. Right-click the command button, and then click Format Control.
2. On the Properties tab, click Move and size with cells.
3. Click OK.

so maybe you could use a macro to fix the controls on the sheet, eg if you want to change every OLEObject, you could do this:-

Option Explicit
Sub testme01()

    Dim OLEObj As OLEObject
    Dim wks As Worksheet
    
    For Each wks In ActiveWorkbook.Worksheets
        For Each OLEObj In wks.OLEObjects
            OLEObj.Placement = xlMoveAndSize
        Next OLEObj
    Next wks
    
End Sub

If you want to be more conservative and only fiddle with the checkboxes:

Sub testme02()

    Dim OLEObj As OLEObject
    Dim wks As Worksheet
    
    For Each wks In ActiveWorkbook.Worksheets
        For Each OLEObj In wks.OLEObjects
            If TypeOf OLEObj.Object Is MSForms.CheckBox Then
                OLEObj.Placement = xlMoveAndSize
            End If
        Next OLEObj
    Next wks
    
End Sub

Regards
Ken.............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------