+ Reply to Thread
Results 1 to 6 of 6

one macro to run multiple checkboxes

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    Rockville, MD
    MS-Off Ver
    Excel 2003
    Posts
    47

    Question one macro to run multiple checkboxes

    I have a worksheet I am developing that will eventually have about 3,000 checkboxes. I found a macro posted online to create the checkboxes, which works great. I also have a macro which will run an individual checkbox to do the following to the adjacent cell if the checkbox is clicked: change the cell value, lock the cell, and format the cell. Both sets of code work great. The problem is I will have to create 3,000 macros with 3,000 different names and open 3,000 checkboxes to assign the appropriate macro. I want to assign one macro to every checkbox as it is created that assigns a universal macro which simply says, if this checkbox is clicked, then make changes to the adjacent cell. I added an .onAction command to the below insertCheckboxes code to assign a macro called CheckBoxUniversal_Click. That works, now I need to create the code for CheckBoxUniversal_Click which will tell excel to make changes to the adjacent cell. Thoughts?

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 09-11-2012 at 03:51 PM. Reason: Added code tags for new user...please do so next time

  2. #2
    Registered User
    Join Date
    09-11-2012
    Location
    Rockville, MD
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: one macro to run multiple checkboxes

    Ok, I've made some progress, but still need help! Here's where I am.

    I have a workbook I am developing that will eventually have about 3,000 checkboxes across multiple worksheets. I found a macro posted online to create the checkboxes, which works great. I also have a macro which will run an individual checkbox to do the following to the adjacent cell if the checkbox is clicked: change the cell value, lock the cell, and format the cell. Both sets of code work great. The problem is I will have to create 3,000 macros with 3,000 different names and open 3,000 checkboxes to assign the appropriate macro. I want to assign one macro to every checkbox as it is created that assigns a universal macro which simply says, if this checkbox is clicked, then make changes to the adjacent cell.

    I added an .onAction command to the "create checkboxes" code assign a macro called CheckBoxUniversal_Click.

    That works, now I need to create the code for CheckBoxUniversal_Click which will tell excel to make changes to the adjacent cell. I borrowed and edited some code which does what I want, except it only works with a small number of checkboxes. When I put it into my worksheet with hundreds of checkboxes, it goes nuts trying to complete the "for each" loop.

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 09-11-2012 at 03:52 PM. Reason: Added code tags

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: one macro to run multiple checkboxes

    let's leave the issue of having 3000 checkboxes in a workbook alone for now (are you nuts? ;-P)

    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    09-11-2012
    Location
    Rockville, MD
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: one macro to run multiple checkboxes

    JP,

    First, I want to take a moment to thank you for laughing at my pain . Yes, I must be nuts for taking on this project. The 3,000 checkboxes was NOT my idea.

    Thanks much for the code. I'll pop it in first thing in the a.m. Looks great! Thanks again.

    Shoshana

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: one macro to run multiple checkboxes

    you're welcome
    sounds like a lot of fun! ;-)

  6. #6
    Registered User
    Join Date
    09-11-2012
    Location
    Rockville, MD
    MS-Off Ver
    Excel 2003
    Posts
    47

    Re: one macro to run multiple checkboxes

    JP,

    Works great, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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