"De"-select a forms control programmatically
 

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

"De"-select a forms control programmatically
 

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" wrote in message
...
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

"De"-select a forms control programmatically
 

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

"De"-select a forms control programmatically
 

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

"De"-select a forms control programmatically
 

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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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