+ Reply to Thread
Results 1 to 11 of 11

Assign 1 Macro to Run Multiple Checkbox Controls

  1. #1
    Registered User
    Join Date
    02-25-2007
    Posts
    34

    Assign 1 Macro to Run Multiple Checkbox Controls

    I have over 100 checkboxs on an excel sheet. This sheet will be duplicated 25 times in this workbook.

    How can I move this code so its not on each sheet.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Jay59,

    Since the code is not event specific to the Worksheet, place the code in a Standard VBA module. This will make the available to all other code and objects in your project.

    Please Login or Register  to view this content.
    Placing a Macro in a Standard Module
    1. Copy the macro code above using CTRL+C
    2. Open your workbook and Right Click any Sheet Tab
    3. Click View Code in the pop up menu.
    4. Press ALT+I to activate the Insert Menu.
    5. Press M to insert a Standard Module
    6. Press CTRL+V to Paste the code in.
    7. Press CTRL+S to Save the macro code.

    Sincerely,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310
    I had this question in the past and discovered for myself what I think is the most efficient and simple way to do this

    This will work with shapes from the Shapes Toolbar and controls from the Forms Toolbar

    One macro that can be used for all the checkboxes with one line of code that will tells you which checkbox was clicked
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-25-2007
    Posts
    34
    Its an activex control, not a form control.

    I guess I need to change all the checkboxs to form control.

    I had a friend do this code, but it gets an error

    For Each shp In Shapes

    Please Login or Register  to view this content.


    I also found a code that lists all the shapes on a spread sheet with all this info.

    Worksheet Shape Type OnAction Hyperlink TopLeft BotRight Height Width Autoshape Form

    So I may be able to create a code to lop through all the rows and create form checkboxs with it.
    Last edited by Jay59; 09-15-2007 at 11:46 AM.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Jay59,

    What is it you want to do? You first post is about repeating a piece of code. Next, you're talking replacing controls?

    Sincerely,
    Leith Ross

  6. #6
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310
    Quote Originally Posted by Jay59
    Its an activex control, not a form control.

    I guess I need to change all the checkboxs to form control.

    I had a friend do this code, but it gets an error

    For Each shp In Shapes
    No I am not trying to compel you to change your activex controls to form controls.

    Somehow I arrived at thinking you had form controls.

    You may have a good reason to be using activex controls.


    You can easily follow what Leith has suggested using your controls.

  7. #7
    Registered User
    Join Date
    02-25-2007
    Posts
    34
    I want to control my checkboxs with one piece of code and not have the code on everysheet, but I was thinking I read somewhere that You have to use Form Controls to do that.

    Doesn't work with the checkboxs I have.

    Do I need to add something to the code to be recoginzed on the active worksheet?
    Last edited by Jay59; 09-15-2007 at 06:31 PM.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Jay59,

    Can you post the code you have so far, or even the workbook?

    Thanks,
    Leith Ross

  9. #9
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310
    Quote Originally Posted by Jay59
    I want to control my checkboxs with one piece of code and not have the code on everysheet, but I was thinking I read somewhere that You have to use Form Controls to do that.

    Doesn't work with the checkboxs I have.

    Do I need to add something to the code to be recoginzed on the active worksheet?
    Right you cannot refer to a shape range by simply using 'shapes'

    But forget that for now.
    Are you sure that you are taking the differences between form controls and activex controls into consideration

    They are not neccessarity interchangable in terms of what you might be doing.

    The value of an activex checkbox and a form checkbox are handled differently

    You should post your code like Leith suggests.

    I cannot imagine that you need 100 checkboxes for this code

    Please Login or Register  to view this content.
    Last edited by SuitedAces; 09-15-2007 at 06:56 PM.

  10. #10
    Registered User
    Join Date
    02-25-2007
    Posts
    34
    This is what I'm doing.

    Here you can see what I have.
    You see 2 checkbox, "NA", and a point value in a row.

    The goal is to have it so that only 1 out of 2 checkboxs or the NA is there.

    So when you check one box it makes the other box = false and clears where it says NA. If you un-check the box and the other box = false then it places "NA" in the field.

    It also adds the value to the right to a table if the second box is checked.


    http://lh5.google.com/jdangler/Ru3BJ...0/untitled.bmp

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Jay59,

    What generates the NA? What does the each check box do when it is checked? Please keep your answers on topic so we can provide you with a solution.

    Sincerely,
    Leith Ross

+ 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