+ Reply to Thread
Results 1 to 2 of 2

Macro to Process CheckBox Clicks?

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    202

    Macro to Process CheckBox Clicks?

    I'm trying to set up what is basically a checkbox-enabled contract builder. On a Terms sheet is a list of about 30 different contract terms and conditions (simple text entries), with a checkbox next to each one. Each checkbox is also linked to a True/False cell adjacent to each entry. The goal is to have the "checked" contract terms appear as line items on a separate Contract sheet.

    Currently, I have a macro that clears and re-populates the entire Contract sheet (based on the checkbox selections on the Terms sheet). I'd like to set it up so that users can also check or un-check a particular term, and have that change immediately reflected in the Contract sheet (without having to run the macro to re-populate the entire thing).

    My problem is, how do I set up an "event" macro that will run when ANY of the checkboxes is changed? I figured a Worksheet_Change macro would work (since changing a checkbox also changes its linked cell), but apparently changing a checkbox does NOT trigger the Worksheet_Change macro. I don't really want to write 30 separate CheckBox_Click macros...

    So, questions:
    • Apparently one can manually assign a macro to a checkbox. If I write ONE macro (called maybe AnyCheckBoxClicked), can I then assign that same macro to ALL the checkboxes?
    • If the answer to the above is Yes, then how can I get the macro to know WHICH checkbox was clicked? If I could reference the cell that's linked to the checkbox, that would give me the row number of the contract term I need to work with, which would be sufficient. But I don't know to reference the "cell that's linked to the checkbox that just got changed."

    Any help / suggestions would be appreciated. Thanks!

  2. #2
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Macro to Process CheckBox Clicks?

    Here's one way. It has 1 macro that is called by each checkbox like you mentioned. Basically, every time you click a check box, it calls the sub. The sub that is called, clears a range on the Result page, goes down through the list of linked cells, and adds every one that is TRUE back to the range in the Result page that was just cleared.

    All the code is in the worksheet module, but I would move the Update sub to a module. See the attached file.
    Attached Files Attached Files
    Last edited by achammar; 02-04-2022 at 07:13 PM.

+ 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. Checkbox runs macro when clicked, but not when button "clicks" it.
    By excelisfunusually in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-17-2018, 09:39 AM
  2. [SOLVED] Keyboard clicks stop current process then resumes with next line of code
    By patb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2018, 02:26 PM
  3. Macro that double clicks a cell
    By Bredsida in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-15-2016, 05:37 AM
  4. [SOLVED] How to run two macro different on different clicks on same button
    By warriorpoet7176 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-13-2016, 02:05 PM
  5. [SOLVED] Macro to tally clicks per day and then move with the next day
    By Reapz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2015, 02:46 AM
  6. Excel 2007 : A Macro that counts clicks in a cell.
    By markket in forum Excel General
    Replies: 2
    Last Post: 01-14-2012, 06:21 AM
  7. Macro that clicks a Control
    By greenfalcon in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-11-2005, 01:06 AM

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