OLE Combobox: Excel breaks down when it has focus

Hi,

I have ole comboboxes on my Excel97 worksheet.

I have a toolbar with buttons that users click. One of these buttons
does a find Range.Find() call. Everything works fine as long as the
user doesn't have their cursor on/in a dropdown when this call is made.

If they do, I get a Run Time Error 1004 - 'Unable to get the Find
property of the Range class.'

I suspect this is by design as I notice when you give an OLE dropdown
the focus, Excel disables many functions in the standard toolbars.

Is there a way to prevent this from happening? Can I detect whether an
OLE object currently has focus?

Thanks in advance,

Michael D.

Change the TakeFocusOnClick property to False. This is a bug in
Excel 97 that was fixed in Excel 2000.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have ole comboboxes on my Excel97 worksheet.
>
> I have a toolbar with buttons that users click. One of these
> buttons
> does a find Range.Find() call. Everything works fine as long as
> the
> user doesn't have their cursor on/in a dropdown when this call
> is made.
>
> If they do, I get a Run Time Error 1004 - 'Unable to get the
> Find
> property of the Range class.'
>
> I suspect this is by design as I notice when you give an OLE
> dropdown
> the focus, Excel disables many functions in the standard
> toolbars.
>
> Is there a way to prevent this from happening? Can I detect
> whether an
> OLE object currently has focus?
>
> Thanks in advance,
>
> Michael D.
>

I am sure Chip didn't read your post closely enough and saw you say you were
clicking a button.
An Ole Combobox doesn't have a takefocusonclick property (while a
commandbutton does), so as the first command of your macro do


activecell.Activate

to achieve the same effect.

--
Regards,
Tom Ogilvy

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> Change the TakeFocusOnClick property to False. This is a bug in
> Excel 97 that was fixed in Excel 2000.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have ole comboboxes on my Excel97 worksheet.
> >
> > I have a toolbar with buttons that users click. One of these
> > buttons
> > does a find Range.Find() call. Everything works fine as long as
> > the
> > user doesn't have their cursor on/in a dropdown when this call
> > is made.
> >
> > If they do, I get a Run Time Error 1004 - 'Unable to get the
> > Find
> > property of the Range class.'
> >
> > I suspect this is by design as I notice when you give an OLE
> > dropdown
> > the focus, Excel disables many functions in the standard
> > toolbars.
> >
> > Is there a way to prevent this from happening? Can I detect
> > whether an
> > OLE object currently has focus?
> >
> > Thanks in advance,
> >
> > Michael D.
> >
>
>

Yeah, I botched my reply. Sorry.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>I am sure Chip didn't read your post closely enough and saw you
>say you were
> clicking a button.
> An Ole Combobox doesn't have a takefocusonclick property (while
> a
> commandbutton does), so as the first command of your macro do
>
>
> activecell.Activate
>
> to achieve the same effect.
>
> --
> Regards,
> Tom Ogilvy
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>> Change the TakeFocusOnClick property to False. This is a bug
>> in
>> Excel 97 that was fixed in Excel 2000.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I have ole comboboxes on my Excel97 worksheet.
>> >
>> > I have a toolbar with buttons that users click. One of these
>> > buttons
>> > does a find Range.Find() call. Everything works fine as long
>> > as
>> > the
>> > user doesn't have their cursor on/in a dropdown when this
>> > call
>> > is made.
>> >
>> > If they do, I get a Run Time Error 1004 - 'Unable to get the
>> > Find
>> > property of the Range class.'
>> >
>> > I suspect this is by design as I notice when you give an OLE
>> > dropdown
>> > the focus, Excel disables many functions in the standard
>> > toolbars.
>> >
>> > Is there a way to prevent this from happening? Can I detect
>> > whether an
>> > OLE object currently has focus?
>> >
>> > Thanks in advance,
>> >
>> > Michael D.
>> >
>>
>>
>
>

That works perfectly.

Thanks Tom,

Another question about OLE Comboboxes: I am having trouble with the
MatchRequired property.

I set it to true for one of my comboboxes but it doesn't appear to do
anything. The user can click in the combobox, type whatever they want
and then move on. How is it supposed to behave when MatchRequired=True?
Thanks,

the help says:

Specifies whether a value entered in the text portion of a ComboBox must
match an entry in the existing list portion of the control. The user can
enter non-matching values, but may not leave the control until a matching
value is entered.

AND

If the MatchRequired property is True, the user cannot exit the ComboBox
until the text entered matches an entry in the existing list. MatchRequired
maintains the integrity of the list by requiring the user to select an
existing entry.

-------
however after that it says:

Note Not all containers enforce this property.

So perhaps the OleObject container does not. I haven't tested it.

You could change the style of the combobox and force the user to select from
the dropdown list.

--
Regards,
Tom Ogilvy



<[email protected]> wrote in message
news:[email protected]...
> That works perfectly.
>
> Thanks Tom,
>
> Another question about OLE Comboboxes: I am having trouble with the
> MatchRequired property.
>
> I set it to true for one of my comboboxes but it doesn't appear to do
> anything. The user can click in the combobox, type whatever they want
> and then move on. How is it supposed to behave when MatchRequired=True?
> Thanks,
>