How to get two option buttons work for sale prices when clicked RRS feed

  • Question

  • I am creating workbook for learning purposes but will give it out for trials as soon as it works correct. In it I want to have the user have two option buttons for different sale prices. The change of sales may occur after six months, and for this I want when the option 1 is used then the worksheet formulas would have used a CHOOSE function to pick option 1 values for all calculations. When option 2 is ticked then from the last row of data where option 1 ended will not interfere with the new calculations going forward.

    Am just thinking if there could be a way the two buttons will work as if when ever one is clicked it copies the data of the worksheet and past it in the same cells but pasted in values so that the data there becomes values. and going forward the last action turns new formulas on to show the new prices calculated for the cells downwards. this in mind to, the user is not to create any formulas, only data entry is required. hope this is clear enough for assistance.

    Monday, August 14, 2017 9:14 PM

All replies

  • Hi,

    Sorry, your question is somewhat complicated for me. And it's time-consuming for me to reproduce the same sheet as yours. 
    Would you share your Excel file via cloud storage such as OneDrive, Dropbox, etc?
    (Remember to modify/remove private data before sharing)



    • Edited by Ashidacchi Saturday, August 19, 2017 10:05 PM
    Tuesday, August 15, 2017 1:56 AM
  • Hi Jackarbo,

    ->when the option 1 is used then the worksheet formulas would have used a CHOOSE function to pick option 1 values for all calculations

    Do you want to use the option to export data in option 1 to worksheet cell? How would you do this?

    -> it copies the data of the worksheet and past it in the same cells but pasted in values so that the data there becomes values.

    What do you want to do? Where are the data and where are the cells?

    I suggest you provide more details or you could make some instruction on your screenshots so we could try to reproduce your issue. By the way, I also suggest you share the excel file.

    Best Regards,


    Tuesday, August 15, 2017 3:18 AM
  • Hi Terry first of all am sorry I did not get back to you since you replied to my query. I actually travelled and was far from the system all together. Just got back and saw your assistance. am grateful. here is what am trying to achieve. First the sales data sheet is where mostly I want the job to be happening. copying there and pasting there to terminate formulas from recalculating with the new options rates.

    If you look at the middle image of the screen shots we have nine rows filled with data, I will want to see that option 2 button stops that range from recalculating any values when option button is clicked. because it will call on the new rate for new orders. so I think terminating the calculation will be better just copying the earlier data and pasting it as values and then run the new options formulas down from the next available empty row.

    Now this code is doing the job 60% I call it. I wanted to have the code copy for example A1:Q50 and paste it back in the same place as values so that all the formulas will stop working for that particular range. after this I expect to see that row 51 should going down should have formulas base on the option clicked. The copying that happen was because the option button was used to terminate lets say option 1 so all the calculations of it should cease working and not interrupt with option that has now been activated for new sale prices moving forward.

    Further explanation may be in simple terms.

    Option 1 SalePrice is 134

    Option 2 salePrice is 154

    The company used the first option for about 9 months in selling products. A whole range like A1:Q1000 has be using that 134 to multiply quantity of orders, and now change of price has come and that is right in the option two. so all we need is to click on the button so that CHOOSE function will switch to that options value when calculating. Now before that happens we want the code to not jam the range A1:Q1000 with its new rate calculations. It should copy and paste the values in that range and identify the next empty row and begin the new rate calculations there. Now am trying to get DropBox installed to upload the workbook. Its about 28MB and wont upload in OnDrive. That self is worrying, cant see what has made the file that big. I extended my range to row 200.000 for my sale and inventory sheets and formatted them, and the code has run in the sheet so the formulas are working and this has somehow made the application big and very slow. I will tyry and get the book up for further assistance, thank you so much

    Sub OptionButton20_Click()
       Dim Formulas
      Dim R As Range, A As Range
      'Write all R1C1 formulas for D:I into an array
      Formulas = Array( _
        "=IFERROR(IF(PO_DATE="""","""",CHOOSE(LinkCell,WholeSalePrice1,WholeSalePrice2,WholeSalePrice3,WholeSalePrice4)),"""")", _
        "=IFERROR(IF(PO_DATE="""","""",CHOOSE(LinkCell,Tnt_AK1,Tnt_AK2,Tnt_AK3,Tnt_AK4)),"""")", _
        "=IFERROR(IF(PO_DATE="""","""",CHOOSE(LinkCell,AccLoading1,AccLoading2,AccLoading3,AccLoading4)),"""")", _
        "=IFERROR(IF(PO_DATE="""","""",CHOOSE(LinkCell,KumasiOffload1,KumasiOffload2,KumasiOffload3,KumasiOffload4)),"""")", _
        "=IFERROR(IF(PO_DATE="""","""",CHOOSE(LinkCell,ProdCost1,ProdCost2,ProdCost3,ProdCost4)),"""")", _
      'Get the last used cell in column A
      Set A = Range("A" & Rows.Count).End(xlUp)
      'Get the last used cell in column I, then one down and to the left as we have formulas
      Set R = Range("I" & Rows.Count).End(xlUp).Offset(1, -UBound(Formulas))
      'Write the formulas into this row
      With R.Resize(1, UBound(Formulas) + 1)
        .FormulaR1C1 = Formulas
        'Resize till the end of column A if necessary
        If R.Row >= A.Row Then Exit Sub
        'Paste the formulas down
        .Resize(A.Row - R.Row + 1).PasteSpecial xlPasteFormulas
      End With
      Application.CutCopyMode = False
    End Sub

    • Edited by Jackarbo Saturday, August 19, 2017 10:08 PM
    Saturday, August 19, 2017 10:00 PM
  • Hi Jackarbo,

    It seems that you want to apply different WholeSalePrice/Tnt_AK/AccLoading.... for different range according to current selected option. I suggest you not add the process how to select the WholeSalePrice/Tnt_AK/AccLoading in the formulas because it will make previous range re-calculated WholeSalePrice/Tnt_AK/AccLoading as current selected option. You could calculate the WholeSalePrice/Tnt_AK/AccLoading and then add them as constant into the formula.

    Such as

    WholeSalePrice = WorksheetFunction.Choose(LinkCell, WholeSalePrice1, WholeSalePrice2, WholeSalePrice3, WholeSalePrice4)
    Formulas=Array("=IFERROR(IF(PO_DATE="""",""""," & WholeSalePrice & "),"""")")

    Best Regards,


    Tuesday, August 22, 2017 2:38 AM