+ Reply to Thread
Results 1 to 7 of 7

Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

  1. #1
    Registered User
    Join Date
    03-03-2016
    Location
    Anaheim, CA
    MS-Off Ver
    2003
    Posts
    28

    Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    Hi, in the Properties dialog for my CheckBox there is an item labelled "Caption" where I can enter any text and it becomes the caption for the checkbox.

    Instead of entering text, I would like the text from a specific cell to populate the field for the Caption. For example, the text in cell "Rates!B11" would be the caption for the Checkbox. If a user changes the contents of cell B11 then the caption for the checkbox changes as well.

    The checkbox resides on a form called "RentalForm". The following VBA code will temporarily change the caption, but it does not permanently change it as seen in the Properties Dialog.

    RentalForm.Checkbox.Caption = Range("Rates!B11")

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    Try this (change checkbox1 to whatever name is showing for this box in the Properties window)

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    Not sure you can change what is physically written in the Properties box, but you'd have to make your form modeless in order for the caption to update when the sheet is changed.

  4. #4
    Registered User
    Join Date
    03-03-2016
    Location
    Anaheim, CA
    MS-Off Ver
    2003
    Posts
    28

    Re: Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    Thank you Kevin - this worked quite well. There are actually 14 checkboxes on the form and they initialize instantly from the data in B11:B24.

    As StephenR says, there is no way to permanently change the checkbox text in the form as seen in the VBA editor.

    Thanks for your help!

  5. #5
    Registered User
    Join Date
    03-03-2016
    Location
    Anaheim, CA
    MS-Off Ver
    2003
    Posts
    28

    Re: Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    Thank you Stephen, I tried making the form "modeless" and it didn't seem to make any difference. Apperently there is no way to permanently change the form text using VBA code.

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    What exactly do you mean by permanently? Kevin's code should change the caption every time you open the userform.

  7. #7
    Registered User
    Join Date
    03-03-2016
    Location
    Anaheim, CA
    MS-Off Ver
    2003
    Posts
    28

    Re: Can you use cell contents as the caption for a CheckBox in the Properties Dialog?

    Let me clarify so I'm sure we're talking about the same thing. The checkbox caption text appears in two places.
    The first is in the VBA editor properties box when you create the form, and the second is on the form when the user opens the form in Excel.

    When I created the form and checkboxes, I gave the fourteen checkboxes captions "RentalCheckBox1....RentalCheckBox14".

    Using Kevin's code, the form checkbox captions now show whatever text is in Rates!B9:B23, which is exactly what I wanted.

    Now, going back to the VBA editor, the checkboxes still have the original captions "RentalCheckBox1....RentalCheckBox14". Not really a problem - just annoying!

    It would save a lot of code if you could enter a formula in the Caption box of the properties dialog. Instead of entering "RentalCheckBox1" for the caption, I would like to enter =Rates!B9 and then I wouldn't need to initialize 14 buttons on the form.

    Thanks for looking at it again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Properties for Label Caption when using VB Script to change.
    By Marie A. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2014, 08:23 AM
  2. [SOLVED] Userform Checkbox caption
    By TimlmiT in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-03-2014, 10:28 AM
  3. Transfer checkbox caption to different worksheet
    By janicesm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-01-2014, 10:12 PM
  4. trying to copy/paste row based on checkbox caption and cell content
    By Zygoid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2007, 06:09 AM
  5. [SOLVED] How to permanently change Dialog Caption while in the Dialog
    By MikeZz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2006, 01:35 PM
  6. [SOLVED] Adding CheckBox Caption to a Listbox
    By Peterd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2005, 08:05 AM
  7. Cell properties dialog
    By Jos Vens in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-31-2005, 07:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1