Changing the Caption of CommandButtons

[OberonViking]

So I have created this UserForm and a spreadsheet which generates 9 unique options, one of which matches the Japanese for my daughter to improve her Japanese.

Userform image.JPG

I need the CommandButtons to change automatically. I have Named the nine options (CommandButton1 to CommandButton9) but I have not seen a method to change them to match the spreadsheet's choices.

I've seen similar posts and solutions from the past, but not quite what I am after. Any ideas?

[Norie]

Setting the caption of a command button to a value from a worksheet is quite straightforward.

For example, this will set the caption of CommandButton1 to the value in A2 on Sheet2.

Please Login or Register  to view this content.

What do you need the caption(s) to change to and when do you need them to change?

[JBeaucaire]

Changing a CommandButton's caption is easy, you can build something like this right into each CommandButton?_Click event:

Please Login or Register  to view this content.

You can use whatever method works best for you to change dog to a different string, you mentioned sheet names, perhaps.

[OberonViking]

Thank you for your responses.

Norie; where do I slide in that piece of code. I thought and tried unsuccessfully to put in Declarations. Perhaps Initialize is a better option - nope, no luck there either.

Jbeaucaire; this changes the button to the name I want only after I click it - which is something I will utilise in the future. I need the button caption to be

Please Login or Register  to view this content.

or better yet, the Name Option1.

[JBeaucaire]

I highlighted "dog" in blue in the original example to draw attention, you can substitute that with any source text you want.

[OberonViking]

I highlighted "dog" in blue in the original example to draw attention, you can substitute that with any source text you want.

Thank you, yes I understood that part. What I am missing is how to have the CommandButton.Caption equal to a cell value before clicking on it so that as the cell value changes so does the display on the command button. In this way it will work as a series of multiple choice questions.

[JBeaucaire]

You'll have to set the caption correctly once manually the first time.

After that, Norie's shown you how to change it after that.

[OberonViking]

You'll have to set the caption correctly once manually the first time.

After that, Norie's shown you how to change it after that.

I can't have it display a cell value (which is randomised) from the beginning?
Can I get it to run Commandbutton1.Caption = "dog" as it loads the first time?

[JBeaucaire]

Oh, this button is part of a FORM? You can set the starting value of the CommandButton1 in the form's INITIALIZE macro. Use my method or Norie's to set the caption as you see fit.

[OberonViking]

Ahh, I will expore that when I get home. I thought there was something like that but did not know the name.

[OberonViking]

Okay, so where am I going wrong?

Please Login or Register  to view this content.

[JBeaucaire]

To reference a sheet the options are:

Please Login or Register  to view this content.

Each one means something different. If you're trying to reference the sheet by the sheet name, then name goes in quotes, like the third example.

[OberonViking]

Excellent - I had this working as I wanted when it crashed and I lost an hour or two of work.
Trying to think positively, it will help me to remember it in the future, having to type it all out again.

[JBeaucaire]

Hehe, happened to me many times, however my Excel autosaves every 5 minutes so I am always able to recover files after a crash. CTRL-S is your friend.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.