Results 1 to 3 of 3

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

This is a discussion on Place text box values into cells, as user enters them. within the Excel Questions forums, part of the Question Forums category; ******** ******************** ************************************************************************> Microsoft Excel - Budget Template.021.xls ___Running: xl2002 XP : OS = Windows XP ( F )ile ( ...

  1. #1
    New Member
    Join Date
    Jun 2003
    Location
    Brookline, MA
    Posts
    4

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

    ******** ******************** ************************************************************************>
    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
    =

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Phoenix, Arizona
    Posts
    1,711

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

    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

  3. #3
    New Member
    Join Date
    Jun 2003
    Location
    Brookline, MA
    Posts
    4

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

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com