Using Check boxes to toggle cell value ON/OFF - value referenced from another worksheet

[RDA Midwest]

Ok,

I've got most of what I'm trying to do accomplished, but now I would like to expand functionality even further.

I want a checkbox in each cell, on Worksheet 'DCF', from O8 (top left) to AR608 (bottom right)... lots....
This box, when unchecked, should leave the cell empty
When Checked, I want it to reference (use the value of) the same cell locations on Worksheet 'Costs'
and the catch, I need the cells to allow manual entry of any number NOT corresponding to COSTS as well.

Even some simple sample code may help me here, if someone could offer some,
also, due to the number of cells (18,000), I'm hoping there may be a streamlined method of accomplishing this ????

The goal:

This is a financial spreadsheet....
I have written a macro.. Cheating a lot using record to source code
A form allows me to enter some specifics, and then it runs code to open and delimit an ASCII TXT export from proprietary software.
It copies the stuff I want, and pastes values into my DCF Worksheet (With all cell Formats already set as I need them)
Then it tests conditions for any unused rows and deletes them.....

What I want to do now, is open and delimit another file, grab a specific column, paste it into another worksheet named "Costs",
which will have formula to inflate costs for each component, each year, for 30 years.
I can handle this part.

Then, I want my main spreadsheet worksheet named DCF to have checkboxes in each cell that represents the expenditures.
If the box is not checked, the value in the cell should be null/clear/whatever (I don't want it to show $0.00, or FALSE or anything, just blank, but formatted as currency)

If the box IS checked, I want it to reference/be equal to the corresponding cell in the worksheet COSTS.

This way, the client can toggle the expenditure on or off (Changing the year) and it will be at the correct inflated/deflated cost

Follow????

I need the format of the cells with the checkboxes to remain as currency though. Not sure if any of this functionality would hinder that?
All these cells are used within the DCF worksheet to total up annual expenditures, across 30 years, and the DCFws uses formulas withing cells to calculate (So cell format is important)

Also, I need the cell to allow manual entry of a number that doesn't correspond to the COSTSws
So, rather than simply moving the expenditure, say, the client wants to change the cost, they can simply type it in the cell in the DCFws.
I understand that the right way would be to go change it on the COSTSws, but, I don't expect all clients to be that on-point

....

If anyone cares, or if my current code would cause any conflict, here it is
(This stuff, so far, has nothing to do with what I am now trying to accomplish, other than, I need this to run without conflict with the new goal)

I really appreciate any input or help. It's been slow googling and digging for bits and pieces, and this one is proving difficult.
I only started this VBA stuff last week.

Private Sub CommandButton1_Click()


Range("o615") = Val(TextBox2)
Range("l624").Value = TextBox3
Range("l625").Value = TextBox4
Range("n624").Value = TextBox5
Range("j628") = Val(TextBox6)
Range("n620") = Val(TextBox7)
Range("as632") = Val(TextBox8)
Range("as627") = Val(TextBox9)


Workbooks.OpenText Filename:="C:\Reserve\SPREAD.TXT", Origin:=65000, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
        3, 1)), TrailingMinusNumbers:=True
    Range("G7").Select
    Selection.Copy
    Windows("DCF TEMPLATE 200 LINES MINNESOTA DEVELOPER IMPORT 600.xlsm").Activate
    
    Range("O616").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SPREAD.TXT").Activate
    Rows("5:15").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A5").Select
    Selection.Copy
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "Cost Inflation: " & TextBox4
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Version Basis: " & TextBox1
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Report Date: " & Date
    Range("B3").Select
    Selection.ClearContents
    Range("B4").Select
    Selection.ClearContents
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "DCF Directed Cash Flow Modeling Example"
    Range("A6").Select
    
    Range("A1:AJ608").Select
    Range("AJ1").Activate
    Selection.Copy
    Windows("DCF TEMPLATE 200 LINES MINNESOTA DEVELOPER IMPORT 600.xlsm").Activate
   
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
          
    Range("I8:I608").Select
    Range("I608").Activate
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
   
          
          Windows("SPREAD.TXT").Activate
    ActiveWorkbook.Close
    
    UserForm1.Hide


End Sub

 

[RDA Midwest]

Ok, in worksheet DCF I put a checkbox in cell O8
In worksheet COSTS I put 100,000

I can't seem to figure out the code for any of this...

I'm starting with this, but it's not working (Compile error: Else without If):

Sub CheckBox3_Click()

If checkBox3.Value < 0 Then Cell.ClearContents

Else

Sheets("dcf").Range("o8").Value = Sheets("costs").Range("o8").Value
    
End If
    
End Sub

I'm trying to get this functioning... to any degree... I'm lost.

On top of the fact that I need this to work on 18,000 cells.

 

[Norie]

To get that code to work move Cell.ClearContents onto a new line

Sub CheckBox3_Click()

If checkBox3.Value < 0 Then 
    Cell.ClearContents

Else

    Sheets("dcf").Range("o8").Value = Sheets("costs").Range("o8").Value
    
End If
    
End Sub

I'm not sure about the rest of your question, do you have 18000 checkboxes and you want to repeat code like this for them all?

 

[RDA Midwest]

To get that code to work move Cell.ClearContents onto a new line

Sub CheckBox3_Click()

If checkBox3.Value < 0 Then 
    Cell.ClearContents

Else

    Sheets("dcf").Range("o8").Value = Sheets("costs").Range("o8").Value
    
End If
    
End Sub

I'm not sure about the rest of your question, do you have 18000 checkboxes and you want to repeat code like this for them all?

Thanx for your time. I have 18,000 cells I need this function on. And this is the only way I can think to do it .

Im open to suggestions

 

[RDA Midwest]

Is there a streamlined way to accomplish this, verses duplicating this 18,000 times and having to change the button and cell references for each?

Or a different method of achieving this result?

Also, per your suggestion with the code, I made the change but get 424 Runtime Error, Object Required, debugger highlights line two (--> <--)

Sub CheckBox3_Click()


--->If checkBox3.Value < 0 Then <---
    Cell.ClearContents
    
Else


    Sheets("DCF").Range("o8").Value = Sheets("costs").Range("o8").Value
    
End If
    
End Sub

 

[Norie]

Can you post some sample data, or even better upload an example workbook somewhere like Box.net and then post a link to it here?

 

[RDA Midwest]

Here's a quick dummy workbook
https://www.yousendit.com/download/TEhYTmZjR3M4Q1J3SGNUQw

Two worksheets: DCF and COSTS

DCF represents the spreadsheet I provide to clients (Obviously it is not the real one, just a simple mock),
this is where I want to be able to check a box in each cell - in this sample case, I put it in Q12, but in the real world, they would be in all years and cells O2 to AR16

The COSTS worksheet contains costs. You will see that they are in every year and cell, inflated accordingly

So, I want to be able to toggle a cell in DCF, and have it snag a value (cost) from that same (corresponding) cell in COSTS

Imagine all cells in all years have a CheckBox
If it is checked, the cost is there, and if it is not, the cell is otherwise empty (aside from the checkbox)

Follow ???

I should have mentioned: I am using Excel 2010

 

[RDA Midwest]

Also, if it helps you to get your head around the end goal, view this in Adobe Reader
http://rdamidwest.com/files/Sample_Condo_Reserve_Study.pdf

Page 50 begins some example spreadsheets - these are real, but anonymized. And I've also made some minor changes to them.
You will see how the expenditures lay out.
The end goal here is for the client to be able to toggle the cells on or off, so they can easily move expenditures, and still maintain correct inflated/deflated costs.
The checkbox was my first take on how to make this possible.

THANK YOU FOR TAKING THE TIME TO HELP ME

 

[RDA Midwest]

Another thing I noticed... I can delete a row that a checkbox is in, but the checkbox remains.

This will not be feasible... I would need the checkboxes to be deleted with their rows...
Is there a way to link the checkbox to a cell????

I'm starting to think that what I want to do is not going to be possible.... or at least, maybe not with checkboxes