Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running total from userform textbox (2K)

    A user selects from a combobox on a userform a particular department. The user then types in a value into a textbox. I am using this code to add the value to a worksheet to produce a running total. The worksheet contains 2 columns <Department in column A and Running total in column B>. My problem is that the value is always added to B2 irrespective of the department chosen. B2 represents the runnning total for the first item in the combobox. I've done this many times without problems but for some reason I cannot see what the problem is. Help please.

    Dim DepAC As Range
    Set DepAC = Worksheets("Sheet1").Range("A2:A43")
    DepAC.Find (ComboBox1.Value)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = ActiveCell.Value + TextBox1.Value
    ' Clear the Text Box
    TextBox1.Value = ""
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running total from userform textbox (2K)

    False alarm! I realise that I must add <Select> after the <Find> method. However, I'm having difficulty in clearing the text box on opening the form. In the Workbook_Open event I am using:

    TextBox1.Value = ""

    but the text box always displays 6.35 each time I open the workbook. Any ideas?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running total from userform textbox (2K)

    Try putting it in the UserForm_Activate event.

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running total from userform textbox (2K)

    Your code can be shortened to:

    Dim DepAC As Range
    dim oFoundcell as range
    Set DepAC = Worksheets("Sheet1").Range("A2:A43")
    set ofoundcell=DepAC.Find(ComboBox1.Value).Offset(0, 1)
    ofoundcell.Value = ofoundCell.Value + TextBox1.Value
    ' Clear the Text Box
    TextBox1.Value = ""
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running total from userform textbox (2K)

    Thanks Steve. All's well!
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running total from userform textbox (2K)

    Much more elegant code. Thanks, Jan Karel - very useful.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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