ComboBox crashing Excel

[daseffects]

I have a few comboboxes on Excel2000 userforms that have various ranges for row controls and I've set matchentry to complete.

The combobox works fine via drop down and if you spell what you're looking for exactly. However when you type in some different text into the combobox it freezes the screen, sometimes the formating changes and Excel comes to a grinding halt.

I do have the following running w/ the combobox.

Any thoughts? Fixes?

Private Sub Combobox3_Change()
Sheets("Eventsdatabase&quot.Cells(3, 5).Value = UserForm1.ComboBox3.Value
End Sub

 

[SkipVought]

Hi,

I could not reproduce this condition. I'm running 2000.

You are running your ComboBoxes from a UserForm, NOT from a Sheet, correct?

Here's a comment.

When you us the ComboBox_Change event, each keystroke is considered a change. If you have other worksheet_Change event processing this can really clog the pipeline.

Better to key the assignment of Cells(3,5) to some other event.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[daseffects]

I can't replicate it either/ I think I may have some other cells calling for the value in the combobox. Will take another look and take your suggestion on board.

As a quick fix does any one know how set up the combobox so that only values in the rwosource will be accepted? Like a cell data validation?

Thanks.

D

 

[SkipVought]

A ComboBox, by definition combinds the features of a TextBox and a ListBox.

If you want to limit to a fixed list, use a ListBox.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

You could set the .MatchRequired property to true.

This is the Excel Help File explanation for the .MatchRequired property:

MatchRequired Property


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.

Syntax

object.MatchRequired [= Boolean]

The MatchRequired property syntax has these parts:

Part Description
object Required. A valid object.
Boolean Optional. Whether the text entered must match an existing item in the list.


Settings

The settings for Boolean are:

Value Description
True The text entered must match an existing list entry.
False The text entered can be different from all existing list entries (default).


Remarks

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.

Note Not all containers enforce this property.

I hope this helps!




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!