"De"-select a forms control programmatically

  • Thread starter Thread starter H.G. Lamy
  • Start date Start date
H

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
 
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
 
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
 
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.
Click to expand...

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).
Click to expand...

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

Regards,
Peter t
 
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
 
Back
Top