Getting a macro to understand option buttons (excel)

Topic

New Reply

Hello,

I’m trying to develope a macro that will make decisions based on two option buttons for each row of data. My data consist of transmission wire types along with its costs and construction costs. I’m trying to load a macro into a command button that will compute the costs of each wire depending on whether its added (wire cost + construction cost) or replaced (just construction costs). I don’t know how to assign two option buttons to a cell so that I can write a macro to make a decision on that cell. I figure I may have to use IF THEN statments but how do I write the macro to even understand that option buttons are involved. Attached is an example of the spreadsheet that I’m trying to put together. Nothing is loaded into the options buttons nor the command button. Everything is there only as illustrations to understand exactly what I’m trying to do.

I really look forward to any suggestions. Thanks

LaMont

Reply | Quote

Replies

Is this what you are looking for? I did NOT use a macro just formulas. Each one is individually set for add/replace.

When you do a replace I also added cond formatting to cross-out the material cost.
Steve

Reply | Quote

Yes, this is exactly what I want. My reasoning for using a macro however is becuase the two transmission lines may be included in a colunm with a list of other lines that need not to be added or replaced. So excel would have to know whether to include that line based on whether the add or replace option is even chosen at all. Maybe I could put a third option box to each row of data indicating niether, this way I don’t have to use a macro – What do you think? And how exactly do you create the set of two (maybe three) option buttons for each row of data?

Much thanks

LaMont

Reply | Quote

They are from the FORMS toolbar.
Add all that you want
then right-click on any of them
Format control…
control tab
indicate a cell reference for the cell Link (this is where the result will be)
The result is 1 for first button, 2 for 2nd, etc

Since you want different SETS of option buttons, you must add a GROUP BOX around each set. If you do not, and you create 2 in row 1 and 2 in row 2 they will act like a group of 4 radio buttons with only 1 ALLOWED to be selected. By making them multiple groups you allow each one to have a selection.

Actually, in your example instead of 2 radio buttons per line, you could accomplish the same with 1 checkbox per line (“include Manufacturing costs”) to include (checked) or NOT include (unchecked). I wasn’t sure if this was a line-by-line thing or a “global” for all calcs?

You can use a formula with additional option buttons, but I don’t completely understand what you want to do with them so I can’t give more details. If I understand somewhat, You could use 1 checkbox for each line to “include” or “NOT include” tha line in a total and have a “global” check box (at the top of the sheet somewhere) that indicates whether or not ALL the CHECKED lines should “include Mfg Costs” or NOT.

Checkboxes yield true/false (checked/unchecked) and are usually used when you can select ANY combination of checkboxes (all could be checked or NONE or ANYTHING in between). Radio (option) buttons are used when ONLY 1 item from the list is possible. You can use both types depending on what you want to do.

I can provide more help, if you detail more of what you would like. Most of this can be done without code: ONLY formulas, making it faster to calc, “live” (no need to run the macro) so always up-to-date, no macro warning message.

Steve

Reply | Quote

Hey Steve,

Just wanted to let you know that the info you gave worked out just fine. I’m still working out a few minor problems and I am considering the other suggestions.

Thanks a lot

LaMont

Reply | Quote

Hey Steve,

Just wanted to let you know that the info you gave worked out just fine. I’m still working out a few minor problems and I am considering the other suggestions.

Thanks a lot

LaMont

Reply | Quote

They are from the FORMS toolbar.
Add all that you want
then right-click on any of them
Format control…
control tab
indicate a cell reference for the cell Link (this is where the result will be)
The result is 1 for first button, 2 for 2nd, etc

Since you want different SETS of option buttons, you must add a GROUP BOX around each set. If you do not, and you create 2 in row 1 and 2 in row 2 they will act like a group of 4 radio buttons with only 1 ALLOWED to be selected. By making them multiple groups you allow each one to have a selection.

Actually, in your example instead of 2 radio buttons per line, you could accomplish the same with 1 checkbox per line (“include Manufacturing costs”) to include (checked) or NOT include (unchecked). I wasn’t sure if this was a line-by-line thing or a “global” for all calcs?

You can use a formula with additional option buttons, but I don’t completely understand what you want to do with them so I can’t give more details. If I understand somewhat, You could use 1 checkbox for each line to “include” or “NOT include” tha line in a total and have a “global” check box (at the top of the sheet somewhere) that indicates whether or not ALL the CHECKED lines should “include Mfg Costs” or NOT.

Checkboxes yield true/false (checked/unchecked) and are usually used when you can select ANY combination of checkboxes (all could be checked or NONE or ANYTHING in between). Radio (option) buttons are used when ONLY 1 item from the list is possible. You can use both types depending on what you want to do.

I can provide more help, if you detail more of what you would like. Most of this can be done without code: ONLY formulas, making it faster to calc, “live” (no need to run the macro) so always up-to-date, no macro warning message.

Steve

Reply | Quote

Yes, this is exactly what I want. My reasoning for using a macro however is becuase the two transmission lines may be included in a colunm with a list of other lines that need not to be added or replaced. So excel would have to know whether to include that line based on whether the add or replace option is even chosen at all. Maybe I could put a third option box to each row of data indicating niether, this way I don’t have to use a macro – What do you think? And how exactly do you create the set of two (maybe three) option buttons for each row of data?

Much thanks

LaMont

Reply | Quote

Is this what you are looking for? I did NOT use a macro just formulas. Each one is individually set for add/replace.

When you do a replace I also added cond formatting to cross-out the material cost.
Steve

Reply | Quote