Taking focus off control on sheet

[Don Wiss]

In my code I have used various methods to get the focus off of a Control
Toolbox control, like a combo box or check box. One I use is:

SendKeys "{ESC}"

I don't think that this is the best. But I can't find in all of the code
I've written what the other methods I've used are.

Don <www.donwiss.com> (e-mail link at home page bottom).

[Dave Peterson]

activesheet.range("a1").select

(choose a cell of your choice?)

Don Wiss wrote:
>
> In my code I have used various methods to get the focus off of a Control
> Toolbox control, like a combo box or check box. One I use is:
>
> SendKeys "{ESC}"
>
> I don't think that this is the best. But I can't find in all of the code
> I've written what the other methods I've used are.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

--

Dave Peterson

[Don Wiss]

On Thu, 03 Aug 2006, Dave Peterson <[email protected]> wrote:

>Don Wiss wrote:
>>
>> In my code I have used various methods to get the focus off of a Control
>> Toolbox control, like a combo box or check box. One I use is:
>>
>> SendKeys "{ESC}"
>>
>> I don't think that this is the best. But I can't find in all of the code
>> I've written what the other methods I've used are.

>activesheet.range("a1").select
>
>(choose a cell of your choice?)

No. That is the worst way of doing it. You don't know where their cursor is
on the sheet and you want to put it back where it was. And you don't want
the sheet to move.

I had two more I used. One was something about an active window.

Don <www.donwiss.com> (e-mail link at home page bottom).

[Ivan F Moala]

one possible way ??

'// Worksheet code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On error resume next
Set Rg = Target
End Sub


'// Std Module

Public Rg As Range

Sub DoSel()
On Error Resume Next
Rg.Select
End Sub


'// called via your controls code @ the End of the controls code


eg Checkbox code

Private Sub CheckBox1_Click()
DoSel
End Sub

[Dave Peterson]

You should have included more details of what you wanted in your original post.

Don Wiss wrote:
>
> On Thu, 03 Aug 2006, Dave Peterson <[email protected]> wrote:
>
> >Don Wiss wrote:
> >>
> >> In my code I have used various methods to get the focus off of a Control
> >> Toolbox control, like a combo box or check box. One I use is:
> >>
> >> SendKeys "{ESC}"
> >>
> >> I don't think that this is the best. But I can't find in all of the code
> >> I've written what the other methods I've used are.
>
> >activesheet.range("a1").select
> >
> >(choose a cell of your choice?)
>
> No. That is the worst way of doing it. You don't know where their cursor is
> on the sheet and you want to put it back where it was. And you don't want
> the sheet to move.
>
> I had two more I used. One was something about an active window.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

--

Dave Peterson

[Dave Peterson]

ps. maybe something like:

Option Explicit
Private Sub ComboBox1_Change()
ActiveCell.Activate
'your code here
End Sub

If you're using a control (like a commandbutton from the control toolbox
toolbar), you can change the .takefocusonclick property to false.

Dave Peterson wrote:
>
> You should have included more details of what you wanted in your original post.
>
> Don Wiss wrote:
> >
> > On Thu, 03 Aug 2006, Dave Peterson <[email protected]> wrote:
> >
> > >Don Wiss wrote:
> > >>
> > >> In my code I have used various methods to get the focus off of a Control
> > >> Toolbox control, like a combo box or check box. One I use is:
> > >>
> > >> SendKeys "{ESC}"
> > >>
> > >> I don't think that this is the best. But I can't find in all of the code
> > >> I've written what the other methods I've used are.
> >
> > >activesheet.range("a1").select
> > >
> > >(choose a cell of your choice?)
> >
> > No. That is the worst way of doing it. You don't know where their cursor is
> > on the sheet and you want to put it back where it was. And you don't want
> > the sheet to move.
> >
> > I had two more I used. One was something about an active window.
> >
> > Don <www.donwiss.com> (e-mail link at home page bottom).
>
> --
>
> Dave Peterson

--

Dave Peterson

[Don Wiss]

On Fri, 04 Aug 2006, Dave Peterson <[email protected]> wrote:

>Option Explicit
>Private Sub ComboBox1_Change()
> ActiveCell.Activate
> 'your code here
>End Sub

Yes, that looks like it would work.

Thinking about where I have done this before I found this one:

Range(ActiveCell.Address).Select

Then I think the other I've used was something like: ActiveWindow.Activate

I gather any of these are better than sending an escape.

>If you're using a control (like a commandbutton from the control toolbox
>toolbar), you can change the .takefocusonclick property to false.

(1) I never use a button from the Control toolbox. I see no advantages.

(2) I do use ComboBoxes, CheckBoxes, and OptionButtons, usually as I want
to be able to hide them. For some reason not all of the Forms controls are
hideable.

I did not find the .takefocusonclick property on the ComboBox properties
list. That is the only control I had a chance to check.

Don <www.donwiss.com> (e-mail link at home page bottom).

[Tom Ogilvy]

Range(ActiveCell.Address).Select
would be redundant compared to
ActiveCell.Activate
or
Selection.Select

and if the user had multiple cells selected, would not disturb them as your
choice would.

CommandButton is the only control that has a takefocusonclick property.

--
Regards,
Tom Ogilvy

"Don Wiss" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On Fri, 04 Aug 2006, Dave Peterson <[email protected]> wrote:
>
>>Option Explicit
>>Private Sub ComboBox1_Change()
>> ActiveCell.Activate
>> 'your code here
>>End Sub
>
> Yes, that looks like it would work.
>
> Thinking about where I have done this before I found this one:
>
> Range(ActiveCell.Address).Select
>
> Then I think the other I've used was something like: ActiveWindow.Activate
>
> I gather any of these are better than sending an escape.
>
>>If you're using a control (like a commandbutton from the control toolbox
>>toolbar), you can change the .takefocusonclick property to false.
>
> (1) I never use a button from the Control toolbox. I see no advantages.
>
> (2) I do use ComboBoxes, CheckBoxes, and OptionButtons, usually as I want
> to be able to hide them. For some reason not all of the Forms controls are
> hideable.
>
> I did not find the .takefocusonclick property on the ComboBox properties
> list. That is the only control I had a chance to check.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).