Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
4.1k views
in Technique[技术] by (71.8m points)

excel - how can I create a global variable to reset value every time textbox value changes?

I have a remaining budget value (txtETLAfterFunding), which is the roduct of (txtELTUnReqdDollars) - (txtBudget). If the txtETLAfterFunding is in the negative, I can use txtAdjustmentAmt to offset that negative budget.

i.e. txtETLAfterFunding = -$5,000, so I type in $5,000 in to the txtAdjustmentAmt and the txtETLAfterFunding amount is now $0. But say I mess up and want to type in $15,000 in txtAdjustmentAmt. Now the remaining budget balance adds that to the $0 from the last calc, where I want it to reset back to -$5,000 so that I can then add $15,000 to that and make the remaining budget at $10,000.

I know I need to put my txtELTAfterFunding as a global variable in order to have it reset, but I am lost on how to do that.

code:

Private Sub txtAdjustmentAmt_AfterUpdate()
    If optReduceReq = True Then
        txtELTAfterFunding.Value = CDbl(txtAdjustmentAmt.Value) + CDbl(txtELTAfterFunding.Value)
    End If
txtELTAfterFunding.Value = Format(txtELTAfterFunding, "$#,##.00")
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You could just set txtETLAfterFunding to be calculated as

txtETLAfterFunding = txtELTUnReqdDollars - txtBudget + txtAdjustmentAmt

You would then put the calculation into e.g. "Private Sub ReCalc", which you can call from the _AfterUpdate of the 3 textboxes.

As an aside, your Format code should probably read Format(txtELTAfterFunding, "$#,##0.00").


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...