Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


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

16 Jul 2012   #1

Windows 7 Home (x64)
 
 
Excel: How can I insert the value of cell into ActiveX button caption?

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

My System SpecsSystem Spec
.

16 Jul 2012   #2

Windows 7 Ultimate x64 SP1
 
 

Yes, you can do that, but not with macros. You'll need to use Excel VB (ie: programming) to do it.
My System SpecsSystem Spec
16 Jul 2012   #3

Windows 7 Home (x64)
 
 

So you're saying I should have VB load my desired button caption upon loading the file?
My System SpecsSystem Spec
.


16 Jul 2012   #4

Windows 7 Ultimate x64 SP1
 
 

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?
My System SpecsSystem Spec
16 Jul 2012   #5

Windows 7 Home (x64)
 
 

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.
My System SpecsSystem Spec
16 Jul 2012   #6

Windows 7 Ultimate x64 SP1
 
 

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.
My System SpecsSystem Spec
16 Jul 2012   #7

Windows 7 Home (x64)
 
 

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.
My System SpecsSystem Spec
16 Jul 2012   #8

Windows 7 Ultimate x64 SP1
 
 

Nope. There's no way to do it without using VBA, I'm afraid.
My System SpecsSystem Spec
16 Jul 2012   #9

32bit: XP, Win7 H.P. / 64bit: 2008R2, Win7 Pro, Ultimate / Several flavors of Linux
 
 

Quote   Quote: Originally Posted by Peatawn View Post
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
My System SpecsSystem Spec
16 Jul 2012   #10

Windows 7 Ultimate x64 SP1
 
 

No, he wants to dynamically change the label.
My System SpecsSystem Spec
Reply

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




Thread Tools



Similar help and support threads for2: Excel: How can I insert the value of cell into ActiveX button caption?
Thread Forum
Excel cell Microsoft Office
Excel 2010 Multiply cell Microsoft Office
How to fix caption button size smaller than 18 ? Themes and Styles
Windows Font size for Tab Control /button caption or form too Small Customization
Need interpretive hyperlink preferably in cell within Excel Microsoft Office
Excel 2007 Home and Studentl: calculation results in blank cell Microsoft Office
make excel automatically insert a new row when you hit enter? Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 03:13 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33