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

Page 1 of 2 12 LastLast

  1. Posts : 23
    Windows 7 Home (x64)
       #1

    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 Computer


  2. Posts : 2,913
    Windows 7 Ultimate x64 SP1
       #2

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


  3. Posts : 23
    Windows 7 Home (x64)
    Thread Starter
       #3

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


  4. Posts : 2,913
    Windows 7 Ultimate x64 SP1
       #4

    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 Computer


  5. Posts : 23
    Windows 7 Home (x64)
    Thread Starter
       #5

    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 Computer


  6. Posts : 2,913
    Windows 7 Ultimate x64 SP1
       #6

    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 Computer


  7. Posts : 23
    Windows 7 Home (x64)
    Thread Starter
       #7

    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 Computer


  8. Posts : 2,913
    Windows 7 Ultimate x64 SP1
       #8

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


  9. Posts : 1,030
    Linux Mint / XP / Win7 Home, Pro, Ultimate / Win8.1 / Win10
       #9

    Peatawn said:
    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 Computer


  10. Posts : 2,913
    Windows 7 Ultimate x64 SP1
       #10

    No, he wants to dynamically change the label.
      My Computer


 
Page 1 of 2 12 LastLast

  Related Discussions
Our Sites
Site Links
About 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 02:14.
Find Us