+ Reply to Thread
Results 1 to 11 of 11

Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

  1. #1
    Registered User
    Join Date
    01-28-2022
    Location
    Greece
    MS-Off Ver
    ms office 2016
    Posts
    24

    Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Hello all

    I m trying to figure out what is happening with CheckBoxes

    What i want is this. I insert a checkbox from FORM CONTROLS in the sheet. I want when this checkbox is checked, then A1 cell to take value 5 and when unchecked the value 0 .. this simple thing.

    (In my fact my problem is that i cannot find the name of the checkbox since I have change it many times... How can i find what is the name and how can i call it to run a sub ? )

    thank you
    Last edited by AliGW; 02-08-2022 at 10:09 AM. Reason: Title changed - please think more carefully about your thread titles in future!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,049

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are still new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,114

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    The name should appear in the name box, next to formula bar.
    Or you can use ALT+F10 to display selection pane. All shapes will be listed.

    you could use code
    Please Login or Register  to view this content.
    Although no code is necessary if you Link the checkbox to B1 you can then use a formula in A1
    =IF(B1,5,0)
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    01-28-2022
    Location
    Greece
    MS-Off Ver
    ms office 2016
    Posts
    24

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Ok, I found the name of the checkbox but the code above is not working for me

    What is the "application caller" ?

    I have a code in my sheet1 and I want when this checkbox (in my case checkbox5) checked to place "1" in A1 cell and when unchecked to place "0" at A1 cell. Also my code as below does not work as well

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,114

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    you said the control was from Forms, where as your code would suggest ActiveX control.

    If you assign the macro to the form control then Application.Caller will contain the name of the shape that was clicked.

    You also seem to have changed what you want put in A1. first it was 5 and 0, now it's 1 and zero.


    If it is a activex control then you could use
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-28-2022
    Location
    Greece
    MS-Off Ver
    ms office 2016
    Posts
    24

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Yeah sorry for the confusion I was trying something different.. Forget the other and lets stick in the initial thread. Lets make it simple.. I have a checkbox (Form control) and this check box is the checkbox5

    When I copy paste your code , nothis is happening. Should i change something ?

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,114

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Just assign the macro to the control.

    Example shows control and code in module1
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-28-2022
    Location
    Greece
    MS-Off Ver
    ms office 2016
    Posts
    24

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Thank you. The attached file works perfectly. I have to check my code why it does not run to me... probably i run some protection in cells for other routines.

    update. please check message below
    Last edited by Darknezz; 02-08-2022 at 12:39 PM.

  9. #9
    Registered User
    Join Date
    01-28-2022
    Location
    Greece
    MS-Off Ver
    ms office 2016
    Posts
    24

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    Just one more question.. In your attached file, the Checkbox name is Checkbox2, this name is also appeared in the box/tab left of the formula tab... but in your code the sub is the CheckBox1 ()

    If I have 5 checkboxs how can I determine which checkbox is which and do different things ? This was my question as well.. I mean in your code there is no any reference in the checkbox name or identiy

  10. #10
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,904

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    You could create a structure like this and assign it to every CheckBox.

    Couldn't post the code here because Firewall error. See attached file.
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,114

    Re: Checkbox Question: when checked, then A1 cell to take value 5 and when unchecked 0

    The name of the macro is autogenerated, in this case it reflected the name of my form control. But you can call it what ever you want.

    As I said previously, when macro is called by clicking a shape, which has the macro assigned to it, the application.caller will contain the name of the shape.

+ 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. more checkbox question
    By QuiGon in forum Excel General
    Replies: 1
    Last Post: 11-19-2010, 04:19 AM
  2. A Checkbox Question
    By Rick K in forum Excel General
    Replies: 4
    Last Post: 08-19-2009, 08:46 AM
  3. checkbox question
    By aaronf in forum Excel General
    Replies: 2
    Last Post: 02-19-2008, 03:04 PM
  4. CheckBox question
    By Mike in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-04-2006, 10:25 PM
  5. [SOLVED] Checkbox question
    By Patrick Simonds in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2005, 01:05 PM
  6. [SOLVED] CheckBox question
    By Frank Rudd via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2005, 08:05 PM
  7. Checkbox Userform Question
    By derekrap in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2005, 04:22 PM
  8. Simple Checkbox Question
    By teresa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-03-2005, 05:06 PM

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