RDA Midwest
New Member
- Joined
- Oct 3, 2012
- Messages
- 12
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.
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.
Code:
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