OLE Combobox: Excel breaks down when it has focus

[deathyam]

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.

 

[Chip Pearson]

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

 

[Tom Ogilvy]

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.

 

[Chip Pearson]

Yeah, I botched my reply. Sorry.


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

 

[deathyam]

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,

 

[Tom Ogilvy]

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.