Search code examples
vbaexcelmacrosexcel-2007

Excel Dropdown move when running macro

I'm having Trouble with an Excel sheet I'm programming.

The sheet contains several Dropdown lists where the User can select data. Once the the User is finished filling in his data he clicks a butten that runs a vba script for printing, exporting and emptying the form.

My Problem now is when I run the script all goes as it should except the Dropdown forms are being moved and resized very tiny.

I'm using this Code to clear the Content of the form:

Sheets("Inl1").Range("A9").Select
    Selection.ClearContents

The Settings for the drop down is Locked = True

The Excel sheet is protected.

I'm using Excel 2007. Is there something I've missed?

EDIT:

The resize happens during Export.

s = Range("C31").Value
Sheets(Array("Front", "Sheet", "Sheet2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
s, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False

EDIT 2:

This is the Excel Form before I run the script for PDF Export: Form before

This is the Excel Form after I ran the script: enter image description here

Solution

  • I found the following fix

    • Click Start, click Run, type regedit in the Open box, and then click OK.
    • Locate and then select the following registry subkey:

    HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

    • On the Edit menu, point to New, and then click DWORD (32-bit) value.
    • Type MultiSheetPrint, and then press Enter.
    • In the Details pane, right-click MultiSheetPrint, and then click Modify.
    • In the Value data box, type 1, and then click OK.

    • Select the following registry subkey again: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

    • On the Edit menu, point to New, and then click DWORD (32-bit) value.

    • Type LegacyAnchorResize, and then press Enter.
    • In the Details pane, right-click LegacyAnchorResize, and then click Modify.
    • In the Value data box, type 1, and then click OK.
    • Exit Registry Editor.