activeX controls loose position

[M. Haitz]

Hello,

I´m developing an Excel application using ActiveX controls (Checkboxes, Optionbuttons, Textfields) on a sheet.

After call of the 'printout' procedure during the WorkBook.BeforePrint event procedure for this sheet the controls loose their x-position and move all to the right side of the screen.


Does anybody know how to solve this problem?

Regards
Michael

[CyberJar]

Hi,

Can you show us a little bit of your code for the PrintOut routine?

Thanks,
CyberJar

[CyberJar]

Hello again,

You may be incorrectly placing the PrintOut routine in the Workbook_BeforePrint event.

You may already know this but, BeforePrint occurs when the user requests a print or a print preview, but before the printing or previewing actually occurs.

So are you setting up the Page Setup or something in the Workbook_BeforePrint Sub?

CJ

[M. Haitz]

Hello,

thank you for your attention.

Yes, I know that BeforePrint occurs before printing occurs.

I needed to format the print result in dependency of some conditions, so I had to do some modifications to the current PageSetup.
Therefore I also disabled the systems print event (Cancel = TRUE);

During a singleStep execution I could see, that the bad behaviour of the ActiveX controls appear immidiately after the '.PrintOut' method.
(The print result is still ok, but after returning from printout the position of the controls has changed).

This behaviour seems to be restricted to Excel XP (even here not with all installations).


Here is some simplified code which I execute in the WorkBook_BeforePrint Sub (shows already the bad beaviour):

===========================================
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim pStr As String, aStr As String

If Not doPrint Then Exit Sub
doPrint = False

With ActiveSheet
' ActiveSheet is here the 'ResultListSheet'

Cancel = True

.PageSetup.Orientation = IIf(ResultListSheet.m_Version <> VID_GENERAL, xlLandscape, xlPortrait)
Dim printOffset As Integer
printOffset = 46

aStr = Trim(Val(ResultListSheet.m_numberOfResultListLines + printOffset))

pStr = IIf(ResultListSheet.m_Version <> VID_GENERAL, "$A$1:$O$", "$A$1:$M$")

.PageSetup.PrintArea = pStr & aStr

.PageSetup.PrintTitleRows = "$44:$46"

Dim colStr As String
colStr = IIf(ResultListSheet.m_Version <> VID_GENERAL, "A:O", "A:M")

.PageSetup.PrintTitleColumns = .Columns(colStr).Address
.PageSetup.Zoom = 90
On Error Resume Next

'----------------------------------------------------------------------
' everything is still ok
.PrintOut
' now the ActiveX controls positions have changed
'----------------------------------------------------------------------

On Error GoTo 0
End With
doPrint = True
End Sub
=============================================

Regards
Michael

[CyberJar]

Hello,

I am not a pro in this area, but someone had told me this:

"The thing to bear in mind with the Top and Left properties for controls is that they are measured from the top left of the form, not from the screen. "

Although you are not using TOP & LEFT, this may still be a clue as to the placement and offset of your Print routine.

As an example here's my Print Routine - mine does not make decisions as yours does but - maybe you will see something to help.

Private Sub cmdPrint_Click()
 On Error GoTo 500
 With MyWorksheet.PageSetup
  .TopMargin = Application.InchesToPoints(0.5)
  .BottomMargin = Application.InchesToPoints(0.5)
  .LeftMargin = Application.InchesToPoints(0.1)
  .RightMargin = Application.InchesToPoints(0.1)
  .FooterMargin = Application.InchesToPoints(0.5)
  .CenterFooter = "Company Name"
  .RightFooter = "Page &P of &N"
  .Orientation = xlLandscape
  .PaperSize = xlPaperLetter
  .Zoom = 80
  .PrintGridlines = False
 End With
 MyWorksheet.PrintOut Copies:=1, Collate:=True
 Exit Sub
500:
 MsgBox Err.Number & " " & Err.Description
 Resume Next
End Sub

Hope it helps,
CyberJar

[M. Haitz]

Thank you for your reply.

Meanwhile I have found the solution for my problem. ActiveX-Controls have a property named 'Placement'. This must be set to 1 to avoid the described behaviour.

This feature seems not to be very well documented.