Solved Excel: How can I insert the value of cell into ActiveX button caption?

[Peatawn]

Hi,

I'm using Office 2007 and I have an Excel file that I have to update often. Therefore I will automate certain tasks using macros accessible by ActiveX Control command buttons. However, I would like for the captions to be relative to information found in certain cells; examples of a caption I would like to have:
• "Clear week [value of cell A5]"
• "Last reporting day for week [value in cell named "_OldestWeek"]".

This will help any other colleague using the spreadsheet not to get confused with the actions of each button.

Is there a way to do this?

Thanks in advance for any help,
Peatawn

 

[kegobeer]

Yes, you can do that, but not with macros. You'll need to use Excel VB (ie: programming) to do it.

 

[Peatawn]

So you're saying I should have VB load my desired button caption upon loading the file?

 

[kegobeer]

How much experience do you have using the Microsoft VB for Office applications? Actually creating modules and subroutines that are accessed by buttons, drop down boxes, form/report headers, etc?

 

[Peatawn]

The experience I have with VB is mostly creating macros for Excel (programming the stuff by hand in VB) and I'm self taught since I've been using and building macros for several years now.

I just don't know the code line to change the value of the caption. Provided the code line I will know what to do with it.

Thanks.

 

[kegobeer]

Without the stuff sitting in front of me, I can't tell you exactly what to change. I do, however, recommend picking up an Excel VB programming book to use as a reference. I have a few for different versions of Excel, and all of them go into detail on how to control ActiveX components via VB.

But, yes - you would have VB in the open or get focus command change the text on the fly.

 

[Peatawn]

Thanks for your help. I kind of figured I would end up doing some programming, but still hoped there would be a way to do without, like directly in Excel. Oh well.

 

[kegobeer]

Nope. There's no way to do it without using VBA, I'm afraid.

 

[GEWB]

Thanks for your help. I kind of figured I would end up doing some programming, but still hoped there would be a way to do without, like directly in Excel. Oh well.

You just want to re-label the control button, correct?

In Excel 2007, click on the Developer tab.
Click the Insert icon.

For ActiveX:

• Select the ActiveX Controls button icon.
• Create/Position the new button.
• Righ click on the button and select Properties.

• In the Properties pop up, edit the Caption line to your button label.
• Close the Properties pop up.

For a Form Control:

• Select the Form Control button.
• Create/Position the new button.
• (Option: assign macro now; if later, close the Assign Macro pop up.)
• Click on the button and edit the text to you button label.
• Click outside of the button.

Regards,
GEWB

 

[kegobeer]

No, he wants to dynamically change the label.

 

[GEWB]

No, he wants to dynamically change the label.

Ah, my bad. I think you are correct - VB is the way to do it.

Regards,
GEWB

 

[Peatawn]

FYI, I've found the code I needed. Simple as a matter of fact.

Under ThisWorkbook > Workbook, the code is:
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("RAID_PRV").ClearWeek.Caption = "Clear week " & ThisWorkbook.Sheets("RAID_PRV").Range("OldestWeek")
End Sub

I'd like to thank everyone who wanted to help and spent the time doing so.
Peatawn