Pop up Data Box that calculates

Good day to all,

I am trying to create a work sheet for capital budgets. I would like to be able to click on a target cell and a pop box appear, the user will then fill in the budget numbers for the categories labeled and when they are done only the subtotal will appear in the cell.

Any help would be greatly appreciated

thanks,
Charlie

Hi

How would you expect the numbers to be gathered? A separate field for each number, or a series of numbers separated by some sort of delimiter?

rylo

Hello Rylo,

I would like i think a seperate field for each number. For example.

Soft costs $50,000
Hard Costs $300,000
Improvements $400,000

And then the total show in cell.

Hi

Ok, on your selected sheet, right click on the tab, view code and enter

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  subtot = 0
  If Target.Cells.Count = 1 And Target.Address = "$C$2" Then
    subtot = subtot + Application.InputBox("Enter Soft Costs", Type:=1)
    subtot = subtot + Application.InputBox("Enter Hard Costs", Type:=1)
    subtot = subtot + Application.InputBox("Enter Improvements", Type:=1)
    Target.Value = subtot
  End If
    
End Sub

Now when you select cell C2, it will ask for the 3 numbers, accumulate them, and put the result into C2.

You don't say where your output is to go, so I've just picked a cell.

rylo

Wow Rylo! Thats great. Much Thanks.

What would i change if i wanted it to all be on one pop up box when it opens?

Hi

A couple of ways I can think of.

1) Have a single input box, but have the 3 amounts separated by a nominated separator (| or ~). Then you could put them into an array and sum them.
2) Create a form that has 3 input boxes to collect the different amounts, then a button to put the sum into the relevant cell.

Got a preference?

rylo

probally number 2

Hi

This is really simplistic, but see if it covers what you are chasing.

913251.xlsm

rylo

That is what I am looking for . Thanks Rylo

Is there a way for the numbers to stay in the pop up box once it is closed and re-opens? Right now once I input the numbers and re-open later to update the fields are blank.

Hi

Here's one way. It doesn't put in the total, but a formula that contains the relevant parts. Then it will break out these parts to the relevant text box when the form is opened.

Before you do things, drag from D2 back across to B2 to make sure that C2 is blank.

Private Sub CommandButton1_Click()
  ActiveCell.Formula = "=" & Val(txtSoftCosts) & "+" & Val(txtHardCosts) & "+" & Val(txtImprov)
  Unload Me
End Sub

Private Sub UserForm_Initialize()
  If Len(ActiveCell.Formula) > 0 Then
    arr = Split(Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1), "+")
    txtSoftCosts = arr(0)
    txtHardCosts = arr(1)
    txtImprov = arr(2)
  End If
    
End Sub

See how that goes.

rylo