Wanting to use Radio Button to run multiple functions. RRS feed

  • Question

  • I am having one heck of a time getting a macro to work and need ya'lls help.

    I am fairly familiar with Excel, but not an expert.

    I am attempting to run a macro on a radio button, that will check multiple checkboxs and set values in multiple cells.

    Example: User wants to add a package deal. User clicks the radio button for the package they want and they get 7 items at 1/2 price, and they also get 4 items at a set base value and the option to choose one of two items at a set base value.

    1. The macro should check the 7 checkboxs for the items that are at 1/2 cost, so that the user can see which items are at half cost when buying multiples of that item and/or multiple items.

    2. The Macro should set item A, B C, and D at a specific value.

    3. The macro should pop up a choice of item E, or item F, and wait for the user to choose and set that item at a base value.

    Can ya'll help me?

    Thanks ahead of time.

    Friday, March 10, 2017 8:58 AM

All replies

  • Here is what I am doing so far:

    I have a set of cells that show up to 3 areas that the customer can use to get their items. Some items are available from all 3 sources, some not. In the following example, the item is available from one source. And there is the possibility of additional items from a package deal that the customer does not have to buy. A promotional item source.

    The F2 cell formula        =IF(SUM(G2)+(J2)>50,SUM((G2)+(J2)-50)/2+50,SUM(G2)+(J2))

    G is the primary source, H is the secondary source, I is the tertiary source, and J is the promotional source that is free.

    Items cost normal up to 50 items regardless of source, then double cost after 50 items.

    This works just fine in the total items bought cell: F2

    The packages also can have stuff on sale. So I am have a macro for those packages that allow the customer to see items that are 1/2 cost and 1/4 cost.

    In this instance, item CRM is at 1/2 cost and has a promotional source of 20 free items.

    So the macro sets the promotional items value at 20(cell J2), and allows the user to buy more from the primary source (cell G2).

    The base formula above needs to account for there being a possibility of the items being 1/2 cost or 1/4 cost due to a package.

    I tried to use a nested IF statement that had the above formula in it for IF this, and again for IF that, but modified to affect the cost.

    It failed due to too many functions.

    I have the Option button macro for the chosen package deal; that sets the promotional value in 6 cells, and to activate the check-box to show the customer the 1/2 cost for 1 item.

    Sub OptionButton540_Click()
        Range("K2:K58").Value = "_"
        Range("L2:L58").Value = "_"
        Range("J2:J58").Value = "0"
        Range("J2").Value = (Range("J2").Value) + 20
        Range("J7").Value = (Range("J7").Value) + 20
        Range("J8").Value = (Range("J8").Value) + 20
        Range("J9").Value = (Range("J9").Value) + 20
        Range("J58").Value = (Range("J58").Value) + 40
        Range("J56").Value = (Range("J56").Value) + 20
        ActiveSheet.CheckBoxes("Check Box 541").Value = xlOn
    End Sub
    This also works fine.

    The problem I am having is being able to either change the formula for cell F2, to reflect the 1/2 cost of the item; or to add a function into the macro that changes the value of either F2 or G2.

    Sorry if I am being scattered with this.

    Any ideas?

    • Edited by Ted-Ebay Saturday, March 11, 2017 7:02 AM
    Saturday, March 11, 2017 6:47 AM
  • Hi Ted-Ebay,

    here, I can see that you want to develop something like Shopping Cart of the Website.

    you have some packages and categories of the goods.

    on which you have set the discount prices like some products are available on the half price.

    I can see that you are using Worksheet to let the user select the packages and using formulas for calculations.

    first of all I want to suggest you that try to store your data correctly with in Worksheet.

    try to create column in the worksheet like product id, product name, price, available units in stock, discount in percentage.

    so you can store all the products in one sheet with all these details.(or you can create a user form to add and store the products in worksheet. looks like professional software).

    then you can display the User form to customer who going to select the package.

    data come from the worksheet where you had store the products.

    all the calculations you can calculate in the user form.

    user can select the package and select the products. discount will be calculated and final amount will be display to the user in user form (like a bill).

    if user submit that data and want to purchase the things then you can send a mail with that product details on your mail id. you will be notified regarding order.

    I think this approach can be a good solution.

    if you will allow the user to interact with worksheet then , It is possible that user can tampered the data or make some changes.

    so if you think that this approach can work for you then try to work in this direction.

    if still you want to perform the calculations on the worksheet then try to upload the picture of your worksheet with desired result. so that we can know what are the input and what output should be generated.

    so we can try to give you suggestion to create functions in VBA to perform calculations.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, March 13, 2017 8:23 AM