.AddItem list and populating combobox with created list

[pallaver]

Good afternoon -

Beginner macro user here having some serious trouble with creating
userforms.
Essentially I would to create a combobox which has a list from number
X to number Y.
In the case below, X is RowWorkMin (which is defined earlier without a
problem), and Y is set at 88.

The code below does not seem to be working.

Where am I going wrong? I have scoured the internet for hours for a
simple solution yet haven't found one, hence posting here. RowWorkMin
may change, but essentially it will be a list of integers in sequence,
i.e. ....13,14,15,16,.... etc.

The next step then which I will probably also need help with is how to
populate the combobox on my userform with this list.

Lastly, I've been reading that the combobox allows you to type in an
entry not present in the list. What if I'd like the option of having
the number I'd like to select typed in instead of selected by the drop
down menu of the combobox? Does this mean that I should make a text
box entry as well to accomodate for this selection/entry style?



Private Sub UserForm_Initialize()
Dim i As Integer

For i = RowWorkMin To 88
cboDirect.AddItem Str(i)
Next i

End Sub


Hopefully this makes sense. Thanks for any help in advance -
neil

 

[Dave Peterson]

I'm not sure how RowWorkMin gets declared and changed, but this worked ok for
me:

Option Explicit
Private Sub UserForm_Initialize()
Dim i As Long
Dim RowWorkMin As Long

RowWorkMin = 77

With Me.cboDirect
.RowSource = ""
.Style = fmStyleDropDownList
.MatchEntry = fmMatchEntryComplete
For i = RowWorkMin To 88
Me.cboDirect.AddItem i
Next i
End With

End Sub

If you want to allow the user to type an entry that isn't on the dropdown list,
you can change the .style to fmStyleDropDownCombo. (I'm kind of confused about
what you wanted.)

 

[pallaver]

Hi Dave,

I entered in your code and I get the same problem: Excel highlights
".cboDirect" and I get the following "Computer Error: Method and data
member still not found." (at least that's what the translation means,
I'm working on a computer in Japan making troubleshooting excel/vba
even harder than it has to be).

To give a brief overview of what is going on, I'm trying to modify a
macro which is already built to add in a drop down combobox allowing
the using to either type in the number or select it from the drop down
menu. I've read though that the combobox allows for entry of only
inputs NOT already in the drop down list, hence my tangent above for a
text box.

That being said though, I still first need to figure out why I'm
getting an error every time I try and create a list. Any clues as to
what's going on?

Since, Neil

 

[Dave Peterson]

Do you have a combobox on that userform named cboDirect? It looked that way
from your code--but I could have guessed wrong.

If a combobox on a userform has a style of fmStyleDropDownList, then the user
can only choose from the items on the list. They can type in a value, but it
has to match an existing entry in that list.

If the combobox on a userform has a style of fmStyleDropDownCombo, then the user
can type any value--on the list or not on the list.

I don't see anything in your code (or mine!) that would cause errors.

 

[pallaver]

Ahh... I had changed the combobox name on the userform because I was
getting frustrated and was trying new things but forgot about that.
Didn't realize that's how the combobox and the list hooked up
together. I think I actually may not be confused anymore about this,
great news there.

Also, the explanation of "fmStyleDropDownList" vs.
"fmStyleDropDownCombo" is just what I've been looking for online this
entire time but been unable to find as well.

I'm sure this would all be easier on a computer with software in
English....

Thanks again for your help.

 

[pallaver]

Couple of questions though for my further understanding:

1. I noticed you had put in "Me." in front of cboDirect, yet the
program works without the "Me." Why did you put the "Me." in? Does
it help with bugs or something?
2. What does this line accomplish: " .MatchEntry =
fmMatchEntryComplete "

Thanks, Neil

 

[Dave Peterson]

Me is a reserved word in VBA. It refers to the thing that owns the code. In
this case, it refers to the userform that owns the code (and that combobox). I
like to qualify my objects (like ranges and comboboxes).
It doesn't hurt to qualify them and sometimes can make debugging much, much
easier.

===
This is from xl2003's VBA's help:

MatchEntry Property

Returns or sets a value indicating how a ListBox or ComboBox searches its list
as the user types.

....

Settings

The settings for fmMatchEntry are:

fmMatchEntryFirstLetter:
Basic matching. The control searches for the next entry that starts with the
character entered. Repeatedly typing the same letter cycles through all entries
beginning with that letter.

FmMatchEntryComplete:
Extended matching. As each character is typed, the control searches for an entry
matching all characters entered (default).

FmMatchEntryNone:
No matching.

 

[pallaver]

I think I understand what the "Me." does, just not sure how that helps
with debugging in the future. Regardless though, every part of my
original question has been answered, I updated the original excel
spreadsheet and just tested it and it works. I've received already
some impressives oohs and pats on the back, and I'm humbled to say you
answered in one day what I couldn't find online in one week. How
morally leveling. Thanks.

 

[Dave Peterson]

It's not just the Me. that's important. It's the qualified objects that make
the debugging easier.

Glad you got it working.