Excel Dropdown move when running macro

Question

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:

This is the Excel Form after I ran the script:

What does you script do when preparing for printing. It is not fully clear from you post where the resize happens. Can post the code. — ib11, May 30 '16 at 07:56
Thanks for your Comment! I checked each step of the code and found out that the resize happens during Export: — Christian Seiler, May 30 '16 at 08:55
I am still having hard time to picture what happens exactly. Can you post a screen grab? Is it not just the view zoom? I can't think with the idea that an export would change formatting. — ib11, May 31 '16 at 05:11
I am not at my desk anymore, but two ideas I have that may help you. Are they properly anchored to their parent cell? And you could try to use the Form ComboBox instead of the ActiveX one. See this post on them if needed: http://stackoverflow.com/questions/37007492/run-macro-when-combobox-is-clicked/37010106#37010106 — ib11, May 31 '16 at 07:25

score 0 · Accepted Answer · answered May 31 '16 at 08:24

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.

Excel Dropdown move when running macro

1 Answers1