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.
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.
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.
Placing a Macro in a Standard ModulePlease Login or Register to view this content.
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
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
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.
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
No I am not trying to compel you to change your activex controls to form controls.Originally Posted by Jay59
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.
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.
Hello Jay59,
Can you post the code you have so far, or even the workbook?
Thanks,
Leith Ross
Right you cannot refer to a shape range by simply using 'shapes'Originally Posted by Jay59
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.
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks