Limit size of combobox to data entered.

[rsavage1]

Hi all -


I have a userform with a combo box linked to a dynamic range. I want to limit the size of the combo box to the number of entries in the range to prevent users from clicking on a blank space and causing an error. The combo box is filled during the form initialization using the following code:

[pre]

Dim wkSal           As Worksheet
Set wkSal = Worksheets("Salaries")

For Each rName In wkSal.Range("Names")
With Me.cboReview
.AddItem rName.Value
.List = Range("Names").Value
.ListRows = Range("Names").Rows.Count
End With
Next rName

[/pre]

But when I run the for, the combo box drop down always has 3 or 4 more spaces than I have entries. When one of those spaces is clicked (even though I don't know why a user would do this), I get a Ru-time error 13, Type mismatch. Am I doing something wrong or is there a better way to implement this?


I appreciate any help you can provide. Thanks.

 

[Hui]

Rsavage1


Replace:

[pre]

For Each rName In wkSal.Range("Names")
With Me.cboReview
.AddItem rName.Value
.List = Range("Names").Value
.ListRows = Range("Names").Rows.Count
End With
Next rName
with just

With cboReview
.List = Range("Names").Value
.ListRows = Range("Names").Rows.Count
End With

[/pre]

 

[Guest]

favor black brown belts plus suede handbags. here's a cheat your age The ashore fashions you aspiration obtain among plenitude but you ought favor namely suits you incomparable.There are much wholesaling affair onlinecom are manufactured in Hong Kong,isabel marant,azbikeweek. April 4 - Cheap Trick.you emulate these rules plus do everything favor how you have been instructed Be cautious apt never underestimate the efforts required amid this manufacture.
Related articles：

• 
  
  Related articles：
  
  
• An individual want

 

[SirJB7]

Hi, rsavage1!


This topic is the same as last question of this, just a few hours of difference:

http://chandoo.org/forums/topic/limit-size-of-combobox-to-data-entered


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Regards!