Place text box values into cells, as user enters them.

[Nicholas]

******** ******************** ************************************************************************>

Microsoft Excel - Budget Template.021.xls ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
Q48G49Q49 =
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
48	Company 1 - first $25,000			[insert company]			25,000	-	-	-	-	-	-	25,000
49	Company 1 - over $25,000						10,000	-	-	-	-	-	-	10,000
Details

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

OK For example, using the above set of cells. When the user clicks in columns J:P, in either row 48 or J49, a text box becomes visible with current focus, in which the user can enter a number. When the text box loses focus, I need to put proper values into rows 48 and 49.

I want all values less than 25K put into row 48, and once the sum of J48:P48 exceeds 25K, all subsequent values are placed into Row 49. For the special case where 25K is exceeded in a column, any value <=25K goes into row 48, and any remainder goes into row 49, and then all subsequent values go into row 49.

Any suggestions? I'm trying to abstract a function, as there are multiple rows that behave this way (same display requirements).

Thanks in advance,

Nicholas

[Travis]

This is specific to your example, but its a start. You could probalby make it dynamic with named ranges. Right click the sheet tab that has your data and go to view code. Paste this in there. If it doesnt work they way you want, maybe the concepts will help. It also has a potential downfall. If you click cancel on the input box, you can then type anything you want in the selected cell and it will not run the code. I suppose you could put the same code in a worksheet_change event to catch that.

Sub worksheet_selectionchange(ByVal Target As Range)
Dim inval As Integer
On Error GoTo err:
If Intersect(Target, Range("J48:P49")) Is Nothing Then
'Selection not in range
Else
Prompt:
inval = InputBox("Enter a Value for " & Target.Address)
    If inval = 0 Then Exit Sub
    If IsNumeric(inval) = False Then
    MsgBox "Invalid entry, must be a number"
    GoTo Prompt
    Else
        If Target.Row = 49 Then
        Target = inval
        Else
            If inval < Target Then
            Target = inval
            Else
            If Range("Q48").Value + inval >= 25000 Then
            Target = 25000 + Target - Range("Q48").Value
            Target.Offset(1, 0) = inval - Target
            Else
            Target = inval
            End If
            End If
        End If
     End If
End If
Exit Sub
err:
If err.Number = 13 Then Exit Sub 'Cancel or Alpha was entered
Call MsgBox(err.Description, , err.Number)
End Sub

[Nicholas]

Thanks! My delay in replying, unfortunately, is that the spec has changed slightly: the columns must now be filled in order (sigh). Here is the "long" code that I'm trying to put into place with VB. There is probably some obscure but easy recursive way to fire this off after any Col data is entered.

Note: [Col]SubConTot = [col]48+[col]49


If [J}SubConTot is >= 25K Then
put 0 in [J]48
put [J]SubConTot in [J]49
Else
put 25K-[J]SubConTot in [J]48
Put [J]SubConTot-25K in [J]49

If [J+K}SubConTot is >= 25K Then
put 0 in [k]48
put [K]SubConTot in [K]49
Else
put 25K-[J]SubConTot in [K]48
Put [K]SubConTot-25K in [K]49

if [J+K+L]SubConTot is >=25 Then
put 0 in [L]48
put [L]SubConTot in [L]49
Else
put 25K-[J+K]SubConTot in [L]48
Put [L]SubConTot-25K in [K]49

if [J+K+L+M]SubConTot is >=25 Then
put 0 in [M]48
put [M]SubConTot in [M]49
Else
put 25K-[J+K+L]SubConTot in [M]48
Put [M]SubConTot-25K in [M]49

if [J+K+L+M+N]SubConTot is >=25 Then
put 0 in [N]48
put [N]SubConTot in [M]49
Else
put 25K-[J+K+L+M]SubConTot in [M]48
Put [N]SubConTot-25K in [N]49

if [J+K+L+M+N+O]SubConTot is >=25 Then
put 0 in [O]48
put [O]SubConTot in [M]49
Else
put 25K-[J+K+L+M+N]SubConTot in [M]48
Put [O]SubConTot-25K in [O]49

if [J+K+L+M+N+O+P]SubConTot is >=25 Then
put 0 in [P]48
put [P]SubConTot in [M]49
Else
put 25K-[J+K+L+M+N+O]SubConTot in [M]48
Put [P]SubConTot-25K in [P]49