+ Reply to Thread
Results 1 to 7 of 7

Macro to run multiple checkboxes

  1. #1
    Registered User
    Join Date
    08-17-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Macro to run multiple checkboxes

    I need your assistance on writing my checkbox macro(s) in Excel 2007.

    I have a workbook that imports a spreadsheet, manipulates the spreadsheet based upon the user’s requirements and then saves spreadsheet as a different name. That spreadsheet then in turn is uploaded into a database and prints the information that is contained in the spreadsheet.

    In the example that I have provided, I have listed five checkboxes, but ultimately there will be approximately thirty checkboxes. There may be anywhere between 3,500 to 7,500 rows of data in the spreadsheet. My checkboxes are currently the ActiveX checkbox.

    I want the user to either check or not check each option. The macro will then modify the character string in the cell(s) depending upon whether the checkbox was checked or not checked.

    My problem is that I cannot get my macro to run and update the cells appropriately using the checkbox. In addition, my current process may not be efficient but I wasn’t sure how else to perform the task.

    I have uploaded the macro and the data spreadsheet. Any input or guidance would be tremendously appreciated.

    I have also provided a copy of my code for my checkbox.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Leith Ross; 08-17-2012 at 02:48 PM. Reason: Added Code Tags

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to run multiple checkboxes

    hi notavirusfan, welcome to Excelforum.

    Re your posted code:

    Please Login or Register  to view this content.
    When using With ... end With construction you must not forget dot before object otherwise you will refer to active object at moment. Instead of using Cells - all the millions of sheets cells it's better and more efficient to use UsedRange as in example above. The posted code must reside in the Sheet module the checkbox sits in.

  3. #3
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to run multiple checkboxes

    your file with corrected code for the first checkbox from the top.

    As to the rest of the coding present in the book it can be cleaned up extensively.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-17-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Macro to run multiple checkboxes

    Watersev,

    Thank you the UsedRange. I hadn't seen that before. I am assuming "UsedRange" is only looking at populated cells?


    I know I have lots of code to clean up, but is it efficient to run each checbox has it's own sub or is there a more efficient way to run through all those checkboxes?

    Thank you.
    Last edited by notavirusfan; 08-17-2012 at 04:06 PM.

  5. #5
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to run multiple checkboxes

    re: Usedrange

    It returns a Range object that represents the used range on the specified worksheet. You can experiment with it and check results.

    re: efficiency

    It does not have any influence on efficiency. It can be done with one code though it would be a bit more complicated. Then you should have either another command button to start the code processing checkboxes or user should have them selected before pressing Run Macro button

  6. #6
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro to run multiple checkboxes

    example for 5 existing checkboxes with processing checkboxes within Get_Solarsoft_Data code. Check required checkboxes and press Run Macro
    Attached Files Attached Files
    Last edited by watersev; 08-17-2012 at 05:26 PM.

  7. #7
    Registered User
    Join Date
    08-17-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Macro to run multiple checkboxes

    Thank you so much Watersev!

    The code you provided works great!

    I would have never thought about placing the code within the Get_Solarsoft_Data sub, much less combining all that code into a single With and End With statement. This is definitely more efficient than calling all those individual subs as I had tried to do in my original code.

    This example also helped me improve my coding in a different workbook.

    Once again thank you so much for your time today, it is greatly appreciated!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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