MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Any easiest way to obtain values from commandbuttons

  Asked By: Bastet    Date: Dec 27    Category: MS Office    Views: 941

I've created 10 commandbuttons on UserForm and named it as cb1, cb2, ... cb10.
For their captions, I labeled it as 1, 2, ... 10. What I need is... when I clik
that particular button VBA will display that particular caption.

The macro that I wrote is as following:

Private Sub cb1_Click()
Msgbox 1
End Sub

Private Sub cb2_Click()
Msgbox 2
End Sub

Private Sub cb3_Click()
Msgbox 3
End Sub

Private Sub cb10_Click()
Msgbox 10
End Sub

I think the above macro is fine when there are only 10 commandbuttons exist.
What about 100 commandbuttons or more? Is there any easiest way?



11 Answers Found

Answer #1    Answered By: Ned Storm     Answered On: Dec 27

Basically, you need one click event for each button.

It is possible to generate and control buttons from within a class module, but
that's an advanced topic.

As a matter of design, you shouldn't have a need for multiple buttons that do
almost the same thing - especially 100 of them. What are you trying to do?

Answer #2    Answered By: Catherine Campbell     Answered On: Dec 27

Actually I'm now trying to create a calendar on my own as my personal xla file.
I know that I can get the program which is provided by Microsoft Excel under
Control Calender 10.0 by right clicking Toolbox and select Additional Control.
By that program, I'm pretty sure that I won't be able to make any modification
to suit my needs. Perhaps, after sometimes I need to change to arabic or thais
calendar or something else.

Answer #3    Answered By: Janet Phillips     Answered On: Dec 27

There are several calendar add-ins created  by Excel vba  experts and available
for free download. Why not begin with one of those? Here is one from Martin
Green's website (tutorial with downloads available):


Answer #4    Answered By: Jacob Bouchard     Answered On: Dec 27

I've seen and even tried it but unfortunately
I can't modify the words month which is exhibited in English. What I need is
those months could be changed or may be someone over here found a calendar
add-in that shows those months in Indonesian language.

Answer #5    Answered By: Theresa Watson     Answered On: Dec 27

If you are making your own calendar, then you have 35 buttons for the days, I
imagine (i.e. five whole weeks) plus a few other controls for months, years,
etc. This is nothing like the "100 commandbuttons or more" you said in your
message. Indeed, it is a simple calendar, that many people have probably played

Give proper information when you ask your questions and you will get more
relevant replies. !!!!

The last home-made calendar I say did exactly this. And it did, indeed have
separate click event code for each button. Also, of course, it had code to
change the caption  for each button  to the date it represented.

Excel doesn't offer many alternatives to this - especially for beginners. It is
certainly possible to define a class module which generates and controls the
button for a single day, and you can then create an array of these to form up
your month - but this really is an advanced topic.

Answer #6    Answered By: Andrew Levensky     Answered On: Dec 27

Now I got a clear figure where by I have to write 35
functions for every buttons I have created. Thanks again for your nice

Answer #7    Answered By: Ella Oliver     Answered On: Dec 27

You really need to learn how to ask appropriate questions.

I have very little doubt that someone somewhere will have created  an Indonesian
language calendar for Excel. Whether they are on this list is a different
question, but you are not going to find them with the subject line you have
used, or the round-about way you have finally arrived at what you want.

Might I suggest you read the following article:

And then perhaps ask a more directed question with a more appropriate subject

Answer #8    Answered By: Alok Iit     Answered On: Dec 27

You *never* did that?.... besides the language difference???

Answer #9    Answered By: Lucinda Hall     Answered On: Dec 27

You don't need to write them. Just double click the buttons (in design mode)
and Excel will create them for you. All you need to do is add the single
subroutine call line to each that passes the button  number to a common
processing routine.

Answer #10    Answered By: Rosie Brooks     Answered On: Dec 27

No, I never did.

I always think before I ask a question. Then draft it, then check that I've
done all the research I can, then finish it and send it.

Doesn't mean that there isn't a glaringly-obvious solution that I've missed.
But it does mean that I've asked the best-directed and best-explained
question I can.

Did you actually look at the message trail for this one? It has gone
through about three or four iterations, and the final question is absolutely
nothing like the original question. If the person had asked a question even
close to the actual need in the first go, it could have been answered
quickly and easily.

As it was, I was dragged into spending time on a question-and-answer session
that ended up being for something that was outside my area of knowledge
anyway (i.e. Indonesian-language code). If I'd known what the question was
actually about, I would have left it to someone with the appropriate local

You may claim not to mean disrespect, but I find it hard to accept that. I
enjoy helping people and think before each reply. In this case, it is not a
case of "lighten up" - frankly that was a fairly light reply - I could have
been seriously miffed. By implying that I did not take it seriously, you
are certainly not paying me respect.

And, yes. I always consider language differences as a mitigating factor
when reading an awkwardly-worded message. I know full well how rotten my
own foreign-language skills are.

BTW the reference I provided -
http://www.catb.org/~esr/faqs/smart-questions.html - should be required
reading for all new group members and is very interesting and instructive
reading for older hands. I commend it to everyone.

Answer #11    Answered By: Fabiano Ferrrari     Answered On: Dec 27

No I won't bother with a specific reply. We don't see eye to eye, but
that's OK.

Didn't find what you were looking for? Find more on Any easiest way to obtain values from commandbuttons Or get search suggestion and latest updates.