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

Nicholas

New Member
Joined
Jun 27, 2003
Messages
4
Budget Template.021.xls
DEFGHIJKLMNOPQ
48Company 1 - first $25,000[insert company]25,000------25,000
49Company 1 - over $25,000 10,000------10,000
Details


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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top