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
Peatawn

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
kegobeer

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
Peatawn

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
kegobeer

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
Peatawn

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
kegobeer

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
Peatawn

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
kegobeer

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
GEWB

Several flavors of Linux (Mint primary) / 32bit XP, Win7 HomeP / 64bit Win7 Pro, Ultimate / 64Win8.1
 
 

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
kegobeer

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
Thread Forum
Can I compare 2 Excel spreadsheets cell by cell?
I have an Excel 2010 workbook comprising a simple organization membership list, consisting of names, addresses, etc., for nearly 2,000 members, grouped into 5 chapters' sheets, in a single Excel 2010 file. Every time I sit down to enter updates for any member, or add new members, etc., I always ...
Microsoft Office
Move caption button in vista style builder
http://i.imgur.com/tzxPccc.jpg i wanna put my close button in the corner, somehow like this: http://i.imgur.com/IosEdQ4.jpg i've tried to change the value both of sizing and content margins, but this close button doesn't move at all. can someone help me? note:
Customization
Excel cell
Office 2010/Excel I accidentally entered an email address into a cell and now I can't get rid of that format in that cell. No matter what I do, whenever I try to enter text into that cell it is always illuminated in blue and is underlined, just like email addresses are. How can I make this cell...
Microsoft Office
Excel 2010 Multiply cell
I am kinda stuck with this issue, basically what I want to do is multiply any number which is put in row H by 10 and put the value in the next cell in row I, how can I do this? Thanks
Microsoft Office
How to fix caption button size smaller than 18 ?
Hi. I found a bug in explorer, which resets caption button size to 18 (if you use smaller size) when you use fullscreen applications (games). It may seem like a trivial issue but I think those icons are still too big and ugly when using value 18. Is there a way to fix it, somehow set it to 14...
Themes and Styles
Windows Font size for Tab Control /button caption or form too Small
I have no idea how it happened but the font type and size of all the captions of controls like tab, button, form text etc had changes and has gone too small. My window color and appereance is all set to size 9 Segoe UI. The Setting seems to have affected some windows and seems to not affect...
Customization


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 07:43.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App