If you have buttons or shapes on an Excel  worksheet, you can get their caption text from a worksheet cell, so the text changes, based on a formula. See how to add the button, create its text, then link the button to cell text instead.

Add a Worksheet Button

In this example, the workbook has a macro to show the total amount of an order. There’s a button on the worksheet, and you click that to run the macro.

If you want to add a button, there are commands on the Developer tab, in the Insert menu on the Controls group.

The button in the Form Controls section is easier to use than the ActiveX controls button, and cause fewer problems, from my experience.

The Form Controls button has an “Assign Macro” command that appears automatically, after you create it. Just choose a macro from the list, and the button is ready to use.

Fancier Buttons

Those Developer tab buttons are okay (if you like grey), but I like to use an Excel shape instead. Shapes give you more formatting options, so you can make your button stand out on the worksheet.

In the Insert tab of the Excel Ribbon, click Shapes, then choose one of the shapes, and click on the worksheet, where you want to add it.

With the shape selected, you can change its height and width, other formatting options, like the fill colour and outline colour. I like to change the Shape Effects too, and give it a round Bevel, so it looks more “button=y”.

Then, to make the shape run a macro, right-click on the shape, and assign a macro to run when you click it.

Add Text to the Button

To add a caption to a shape “button”, just select it, and start typing.

For this button, I typed “Run the Macro”

Format the Button Text

After you add the text, with the button still selected, use the Formatting commands on the Excel Ribbon to make the text look better.

I usually centre the text vertically and horizontally, and choose a bigger font size. Change the font colour too, if necessary, to contrast with the shape’s fill colour.

Change the Button Text

Instead of using static button text though, sometimes it’s nice to have a caption that changes, based on the situation on the worksheet.

In this example, a quantity is entered in cell C1, and customers get a discount if the quantity is greater than 40.

The formula in the cell C11, named TotalPrice, calculates the total price of the order.

=OrderQty * VLOOKUP(C1,B8:C9,2,TRUE)

Formula for Button Text

In cell E2, I’ve added another formula, to check the quantity, and show text based on that amount.

=IF(C1<=40,”See price”, “See discounted price”)

If the quantity is 40 or less, cell E2 will show “See price”. If the quantity is over 40, the result in cell E2 will mention the discount – “See discounted price”

Link Button Text to a Cell

Instead of showing the static text, “Run the Macro”, on the button, here’s how to use the dynamic text from cell E2:

• Click on the button to select it
• Click in the Formula Bar, and type an equal sign:  =
• Click on cell E2, which has the text for the button, and press Enter

NOTE: You might have to reapply some of the formatting after you link the button to the cell.

Now, it the quantity is changed, the button will show the applicable text in its caption.

See the Steps

This animated gif gives you a quick look at the steps.

Switch Languages

For another example of linking shape text to worksheet cells, see the cereal box text in my “Switch Languages” blog post.

First, you select a language from a drop down list.

That changes the text in the worksheet cells, because INDEX and MATCH formulas find the translations in a lookup table.

There is an orange rectangle on the worksheet, with text boxes sitting on top of it. Those text boxes are linked to the worksheet cells.

For example, the text box at the top is linked to cell B4, which shows “Honey Nut Cheerios”, when English is selected.

When the selected language is French, the linked text boxes show the French text from the worksheet cells.

_______________________

Excel Button Text from Worksheet Cell

_______________________

Original source: https://contexturesblog.com/archives/2019/02/28/excel-button-text-from-worksheet-cell/