VBA to set range for formulas (1 Viewer)

[tmyers]

I have read a couple various things on this, but cant quite wrap my head around it.

I am trying to make a module for check boxes. When a check box is checked, it will determine the range to be used. Essentially if a column has formulas that use the range of the A column, I want a "B" check box that when it is selected, sets the range to the B column. This is for a "report" I have built within Excel to show various stages of a project. So if we want to see the financials of stage 1 of the project, we would select that check box but if we wanted say stage 4 we would select that check box. All of the data is within another sheet in the same workbook.

How complicated would this be to do? Or am I over thinking it and it is simple?

 

[Isaac]

So basically you want formulas to 'look' at the true/false value of activeX controls on the sheet?

You can...kind of. go to developer, insert, active x controls, checkbox. insert it somewhere, maybe in B1. then right click checkbox go to properties. then in linkedcell property type B1 and hit enter.
then go out of design mode (in developer tab).

then type in a1: =if(b1=true,"true","false")

you will see how a1 changes as you check the box in B1.

 

[tmyers]

I think I over complicated it. I am trying to just hardcode it so when it is clicked (I switched to using a button), it will just straight up set the cells value to a new formula then use the filldown property.

This however is giving me an application/object defined error.

    Dim wb      As Workbook
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    
    
    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("Customer Copy")
    Set ws2 = wb.Sheets("Overview")

    ws1.Range("B14").Formula = "=IFERROR(IF(Overview!B6 + Overview!C1 = 0, "", Overview!B6 + Overview!C1), "")"
    Range("B14:B200").FillDown

 

[Isaac]

Gotcha.
I use the Autofill method.
And speaking of "gotcha's", one of them is that the autofill method range argument must include the cell which is the source of the fill down.

 

[tmyers]

Gotcha.
I use the Autofill method.
And speaking of "gotcha's", one of them is that the autofill method range argument must include the cell which is the source of the fill down.

Got it.
Any idea why the line that inserts the formula is giving me that object defined error however? It looks right as far as syntax goes, but maybe I missed something.

 

[tmyers]

Figured it out. Rookie mistake involving the quotes. Since I was using them in the formula, I had to enclose them in quotes to not accidently escape the rest of the formula.

Dang quotes.