Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

adding values in combobox

  Asked By: Mada    Date: Nov 17    Category: MS Office    Views: 1222
  

I had made a user form with three combo box CboDay, CboMonth,CboYear
in the initialize user form i had put this cord but i want that it is working
fine but
i want to reduce the coding and wanted that the value between 1 to 30 come in
day . between 1 to 12 in month combobox etc
can you help me in this

Private Sub UserForm_Initialize()
With CboDay
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
CboDay.Value = ""
With CboMonth
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
End With
CboMonth.Value = ""
With CboYear
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"
.AddItem "2011"
End With
CboYear.Value = ""

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Hadil Khan     Answered On: Nov 17

: I had made  a user  form with three combo  box CboDay, CboMonth,
: CboYear in the initialize  user form  i had put  this cord but i
: want that it is working fine but i want to reduce the coding  and
wanted  that the value between 1 to 30 come in day . between 1 to
: 12 in month combobox  etc

Create a sub routine which adds integers to an object. Then
call that sub for each object you are initializing.


Private Sub UserForm_Initialize()

' Initialize Combo Boxes
AddIntegerItems CboDay, 1, 31
AddIntegerItems CboMonth, 1, 12
AddIntegerItems CboYear, 2006, 2011

End Sub


' Assumes an object which supports AddItem method
Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)

Dim iInteger
For iInteger = iRangeLow To iRangeHigh
object.AddItem iInteger
Next iInteger

End Sub

 
Answer #2    Answered By: Dale Jones     Answered On: Nov 17

I have added two links to the group website.

One is Dian's excellent article on how to solve computer problems. It is at
pubs.logicalexpressions.com/.../LPMArticle.asp

The other is a very comprehensive article on how to ask a question on a forum
and is at
http://www.catb.org/~esr/faqs/smart-questions.html

Please note - the person who wrote this article will not answer your
questions.

The advice given will help  you to solve your problems. Then, if you ask a
question in this forum, you will know how to ask it to get the best answer.

There are many other links on the website including an excellent free tutorial
on using VBA from Dian's website.
pubs.logicalexpressions.com/.../LPMFrame.asp
=33

Please have a look at the resources on the group website. They will make you
a better programmer!

 
Answer #3    Answered By: Nicolas Costa     Answered On: Nov 17

i am not able to understood your sub procidure i want some discription for
every command
you used and why

' Assumes an object which supports AddItem method
Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)
Dim iInteger
For iInteger = iRangeLow To iRangeHigh
object.AddItem iInteger
Next iInteger
End Sub

 
Answer #4    Answered By: Djoser Massri     Answered On: Nov 17

First, we'll place the indentation back in. We do not write
computer programs only for machines. We also write them for human
beings and human beings like white space, comments, appropriate
punctuation and consistency. These visual cues make reading code
easier and makes code more maintainable.

Squashingeverythingtogethermakesitharderoneveryoneexceptthemachine.

Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)

Dim iInteger
For iInteger = iRangeLow To iRangeHigh
object.AddItem iInteger
Next iInteger

End Sub


Second, I am not at your beck and call. I do not fetch or roll
over. I will not get your slippers. You need to make an effort to
understand what is happening here and you can do the best reading
the documentation and by experimenting, not by demanding that I
supply an explanation for every line in my solution. If you have
more specific questions, ask them.


Let's look at a single solution not involving a sub routine
call. We'll use the Combo Box control named CboMonth. Here's the
submitted method with some white space added for us poor human
readers. :)

With CboMonth

.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"

End With

The glaring question for an experienced programmer is, "Do
numbers have to be quoted in VBA (or in this particular method)?"
Since the author asked for advice on simplifying the code we can
safely assume he realizes that there is probably a shorter method
available to add these items. The answer to the question above is,
"No. VBA and AddItem do not require numbers to be forced into
strings."

With CboMonth

.AddItem 1
.AddItem 2
.AddItem 3
.AddItem 4
.AddItem 5
.AddItem 6
.AddItem 7
.AddItem 8
.AddItem 9
.AddItem 10
.AddItem 11
.AddItem 12

End With

This is important because it allows us to replace the integers
1 through 12 with a variable whose value represents an integer
without having to first convert that value to a string. While I
would never actually write the following code in practice it does
illustrate a working solution.

Private Sub UserForm_Initialize()

Dim iInteger
With CboMonth

iInteger = 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

iInteger = iInteger + 1
.AddItem iInteger

End With

End Sub

Again, not better code in any sense, but it does illustrate
what the solution I provided does in a long winded fashion. In
VBA, there is a control statement which will loop through a series
of integers or a list of some kind and allow operations on each
value before going to the next value in the list.

We know ahead of time that we need to add items to this
control from a sequence of integers numbered 1 to 12. VBA gives us
a For ... Next loop for this purpose. This code is equivalent to
the code above. On each pass through the loop, iInteger
increments by the value in the "Step" clause.

Private Sub UserForm_Initialize()

Dim iInteger

With CboMonth

For iInteger = 1 To 12 Step 1
.AddItem iInteger
Next iInteger

End With

End Sub


We don't really need the "With" statement and we can leave the
Step clause off if we are incrementing by +1.

Private Sub UserForm_Initialize()

Dim iInteger
For iInteger = 1 To 12
CboMonth.AddItem iInteger
Next iInteger

End Sub

To make this into a more general case, we need to first allow
any object which supports the AddItem method to be used.

Private Sub UserForm_Initialize()

AddMonth CboMonth

End Sub

Private Sub AddMonth(object)

Dim iInteger
For iInteger = 1 To 12
object.AddItem iInteger
Next iInteger

End Sub


Since there are cases where we may want to add other
sequential ranges of integers, we can make this even more general.
We replace the 1 above with the lower value of the range and the
12 above with the upper value of the supplied range.

Private Sub UserForm_Initialize()

' Initialize Combo Boxes
AddIntegerItems CboDay, 1, 31
AddIntegerItems CboMonth, 1, 12
AddIntegerItems CboYear, 2006, 2011

End Sub


' Assumes an object which supports AddItem method
Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)

Dim iInteger
For iInteger = iRangeLow To iRangeHigh
object.AddItem iInteger
Next iInteger

End Sub

Another way to look at this is to replace the general variable
with specific ones. For example, "AddIntegerItems CboDay, 1, 31"
is the same as this. We replace iRangeLow with 1; iRangeHigh
becomes 31; and object becomes CboDay.

Dim iInteger
For iInteger = 1 To 31
CboDay.AddItem iInteger
Next iInteger

Look familiar?

 
Answer #5    Answered By: Sherrie Thomas     Answered On: Nov 17

But now you had created more curioucity .

i had a list like
a123
a124
a135
b112
b113
b115
..........................
aa12
bb13


i want that when ever i type the first later in the combobox  the list with the
first latter common should come in spite of the complite list .Or the highlited
value should move to that.

 
Answer #6    Answered By: Anselma Schmidt     Answered On: Nov 17

I think I saw a box  like that on .Net, but I don't know
how to import .Net controls to VBA projects (if it is possible).
VB allows ActiveX, COM, and third party objects which can
exhibit this behavior. You can do almost anything in VB that
can be done in VBA, but VB will set you back some cash.

I suppose you could capture keydown events where your
ComboBox has focus and then update the selection manually, but
that sounds like a lot of trouble. Better to search Google to
see if someone else has already come up with a similar
solution for a VBA control.

 
Answer #7    Answered By: Dang Tran     Answered On: Nov 17

I want to ask you the entire thing
regarding the Sub procedure so that I & other group members also can make there
own sub procedure in future. I was building a form  Project for my daily work. I
am not a programmer nor I am from any of the software side.
I just started working in VBA . with your help  only .I had not read any VB book
.

 
Answer #8    Answered By: Jamie Roberts     Answered On: Nov 17

but your words did not hurt. Your message seemed to
be hastily written. It seemed to indicate that you might be
unwilling to read the documentation included with VBA. You would
not be alone in that. Many people want a special manual written
just for them. My mother wanted  me to write an MS Word manual like
that.

How we ask questions is as important as how we answer them. If
you provide a question with short terse phrases absent of those
visual cues I mentioned in my last message, then you may be
implying that you can not be bothered to follow the niceties of
civilized society and that you are equally willing to receive
terse replies where the answer is correct, but few of the niceties
of polite society are followed.

If, instead, you would like a nice reply which is easily
understood and in which the question is answered with all the
visual cues human readers expect, like correct spelling, good
sentence structure, proper punctuation and editing which deletes
irrelevant sections of a post, then you should reciprocate when
asking a question.

My comments were meant to motivate you and other readers to
find solutions through reading and experimentation rather than by
sending a message to this list. Use this list when those sources
fail. I subscribe to a number of programming mailing lists and the
number of people who write posts asking if something works
astounds me. Why not just try it to find out if it works?

No one wants to see the number of messages on this list
decrease, but I have found, repeatedly, that as people learn to
read the documentation, first, and ask questions only after
experimentation more questions get asked on the list, not less.
The goals of this list do not only include answering questions.
They also include making us better programmers.

I am relatively new to this list, having been asked to do a
couple of trivial Excel applications for a web client. If I have
admonished you unfairly or have expressed the goals of this list
poorly, please let me know.

 
Answer #9    Answered By: Flynn Jones     Answered On: Nov 17

If the list is used to ask
questions which can be fully answered by typing a keyword and pressing F1 for
the help, or by typing the question into the help  search box  we will be
answering the same old questions over and over. the list would get boring.

The object is to help us all learn to be better programmers. I certainly
learn by solving others problems and in doing so I probably refer to the help
files on average once per two lines of code.

I would again mention the two links I posted on our website. They will help
you solve problems yourself, learn, and ask questions which can be answered
intelligently. Dian's TechTrax site is also a fantastic resource for VBA
information.

 
Didn't find what you were looking for? Find more on adding values in combobox Or get search suggestion and latest updates.




Tagged: