Sleeper: Combining event handler and Activex control formatting

[K. Georgiadis]

I have an Activex control (combo box) with the following code:


Private Sub cboPrice_Change()
Range("C2").Value = cboPrice.Value
End Sub

Because of a glitch in Excel SP3, control boxes jump to the left each time Print Preview is selected. The work around is to right click each control, select Format Control > Properties and then click "Move and size with cells."

The Macro recorder reports that the code for these formatting steps is:


Sub AnchorControl()
ActiveSheet.Shapes("cboPrice").Select
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
End With
End Sub

Obviously, it would be just simple (in fact simpler) to format my four or five controls with a few mouse clicks but, in the interest of learning, wouldn't it be OK to include the formatting code in the event handler?

[Paleo]

Hi K.,

why do you need a macro to do this?:think: why not by control properties?:think:

[Ken Puls]

Hi there,

I guess you could do this (untested)


Private Sub cboPrice_Change()
With ActiveSheet.Shapes("cboPrice")
.Placement = xlMoveAndSize
.PrintObject = True
End With
Range("C2").Value = cboPrice.Value
End Sub


But I think that you would only need to set the control properties once, and that would be permanent, so why do it each time? :dunno

[K. Georgiadis]

I'm going to give it a try, Ken.

Carlos, as I indicated in my original post, I do feel that it would be easier to simply right-click on the control and then got to Format Control>Properties. However, in the interest of higher learning :rotlaugh: I was wondering if I could add the code to the event handler.

[Ken Puls]

Let us know how it works out! :yes

[K. Georgiadis]

Strictly as a matter of ...academic interest, this code combining the event handler and control box formatting only works if



.PrintObject = True


is removed.

I'm going to be just as happy setting the formats manually in Excel!!