Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referring to a checkbox control on a worksheet (Excel 2000/2002)

    How can a formula in, say cell B5 refer to a checkbox control on the worksheet?
    Does the technique differ for ActiveX vs. Form (not Userform) controls?

    I'm surprised I cannot find an answer in the Excel books I have.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    When you create a checkbox, either with VB or with forms, you link it to a cell on a worksheet. Usually I keep this cell hidden by making its text color white. The value of this cell is either TRUE or FALSE and you can refer to this cell in other formulas, HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    Thanx.

    That's what I've been doing, but that's awful.
    There should be some way to refer to the control by name, but I have not found such a method, yet.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    AFAIK you can only refer to the checkbox by name from code (writing VBA). The 'linked cell' field is where the state of the checkbox is available to the formula. In code you can refer to the state directly by using the checkbox's name.

    <img src=/S/clever.gif border=0 alt=clever width=15 height=15> Another idea is to assign a Name to the 'linked cell' (the output of the checkbox). If the linked cell is A1 you can assign a Name (Insert/Names/Define) to A1 called "chkStatus" (or whatever your check box is called). So now you can use the Name "chkStatus" in your formulas and they'd be more readable (since I assume that's the reason you'd like to use the control's name).

    I've tried this in xl97 and xl2000, both work fine. Example:
    =If(chkStatus=True,5,0)

    So in a formula, I used the name of the output of the checkbox control and if it's true, I write 5 in this cell otherwise 0.

    Deb <img src=/S/invisible.gif border=0 alt=invisible width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    I'm trying to avoid having to use a linked cell.

    For example, I may have a worksheet function that has some boolean args.
    I was hoping the user could supply the name of the checkbox as an arg to the worksheet function.
    Instead, the choice appears to be to either use a linked cell, or to manually code the True or False.

    A similar problem might exist with Word Forms, but I've not tried there.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    NAME the linked cells
    They can use the NAMED CELLS in functions

    Steve

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    Using a Name does not avoid using a linked cell.

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    You have a unique way of looking at things <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I don't think it's practical (or good practice) make the user know the name of a control on your worksheet. They just want to use your program, click a button, make it do it's thing, not have to know what secret word to use to make it work. How are you going to tell them this name? It just seems like you're making simple things overly complicated from the user's point of view (and they're the ones who count since they're the ones using your program). Excel is all about linked cells, nothing wrong with them, they make life easier.

    Good luck. Deb

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    What you want is not possible Howard.

    But you could do something else.
    With a control toolbox control you could make a function like this:

    Function CheckBoxValue()
    Application.volatile
    CheckBoxValue = Application.Caller.Parent.CheckBox1.Value
    End Function

    And use that function in other cells:

    =CheckBoxValue()

    But I doubt if that beats having a linked cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a checkbox control on a worksheet (Excel 2000/2002)

    I knew how to use a function.

    I was searching for the Holy Grail, i.e., a way to refer directly to the checkbox within the spreadsheet.

    A similar problem appears to exist in Word.
    I can insert a cross-reference to the page number and paragraph number for the check box, but a cross-reference to the "bookmark text" yields an empty string.

Posting Permissions

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