"De"-select a forms control programmatically

[H.G. Lamy]

Hello,

in a short VBA-macro, I have to select a forms control on a worksheet, and
assign a "cell-link" to.
After that, the control should not stay selected, so that the worksheet user
doesn't accidentally delete it.

To that end, right now the last step in my macro is to select a one cell
range, but that doesn't always seem to be the best solution.
I've tried with Sendkeys {Escape}, in vain.

Any idea how to "de-"select a forms control by VBA ?

Thank you in advance.

Regards,

H.G. Lamy

 

[Peter T]

What's wrong with selecting a cell, eg

ActiveCell.Select

But why are you Select'ing the control in the first place, you don't need to
merely to assign it's LinkCell property

Regards,
Peter T

 

[H.G. Lamy]

Peter, thank you for feed back.

Selecting a cell - if this cell happens to be outside the visible screen
area - makes the cursor jump there. Then the user has to scroll back.

Manipulating a control without prior selecting it doesn't seem to work (I
use XL 2003).

Regards,

hgl

 

[Peter T]

Selecting a cell - if this cell happens to be outside the visible screen

area - makes the cursor jump there. Then the user has to scroll back.

Dim vr As Range
Set vr = ActiveWindow.VisibleRange
If Not Intersect(vr, ActiveCell) Is Nothing Then
ActiveCell.Activate
Else
vr(1).Activate
End If
' bit more if the selection is a pre 2007 embedded chart

But as you don't need to select you don't need that

Manipulating a control without prior selecting it doesn't seem to work (I
use XL 2003).

Dim ole As OLEObject
Set ole = ActiveSheet.OLEObjects("Combobox1")
ole.LinkedCell = "B2"

Regards,
Peter t

 

[H.G. Lamy]

Thank you,

I had recorded the code to change the control's properties, and that code
always works with "select" - but not without. Yours does!

Kind regards,

hgl