Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Add data from excel into form on load (Excel/VBA 2000)

    I have a user form that is used to insert values on a specific page in a
    workbook. That part works fine. I have been asked to add data that is the
    result of previous entries.

    eg text box 1 is used to enter new data
    text box 2 would show a value that is in the spreadsheet

    How do I load the form and populate text box2 prior to data entry?

    Public Sub Form_Load_Art()

    Dim bRow As Integer
    Dim bCol As Integer
    Dim intDesQ As Double 'Text Box on Form
    Dim intDesTD As Double 'Text Box on Form
    bRow = 1
    bCol = 1

    With Worksheets("Art Costing")
    intDesQ = .Cells(bRow + 9, bCol + 2).Value
    intDesTD = .Cells(bRow + 9, bCol + 1).Value
    End With

    frmArt.Show

    End Sub

    When I run this it gets the values from the spreadsheet but does not put
    them into the form.

    Help!




    Thanks
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add data from excel into form on load (Excel/VBA 2000)

    You would use the form Initilize event routine. If you want to put both of those values into the same textbox named TextBox2, then you could do something like this:

    <pre>Public Sub frmArt_Initialize()
    Dim bRow As Integer
    Dim bCol As Integer
    Dim intDesQ As Double 'Text Box on Form
    Dim intDesTD As Double 'Text Box on Form
    bRow = 1
    bCol = 1
    With Worksheets("Art Costing")
    intDesQ = .Cells(bRow + 9, bCol + 2).Value
    intDesTD = .Cells(bRow + 9, bCol + 1).Value
    End With
    Me.TextBox2.Value = intDesQ & " " & intDesTD
    End Sub
    </pre>


    This code must go in the module behind the form (right click on the form and then click "View Code"), not in a normal module. The show method to show the form must be somewhere else.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Add data from excel into form on load (Excel/V

    Legare

    Thanks, but I guess my description was unclear.

    I have a form the start on which are these textboxes:

    Design Hours- Named intDesHrs
    Design Quoted Hours - Named intDesQ
    Design Hours to date - Named intDesTD

    User would input current hours into first text box.

    The next two text boxes would contain the data previously entered into the spreadsheet.
    It is these hours that I want to show the user when the form loads, so that they can see
    what time has been used so far.

    I changed what you sent to the following, so I am assigning the value to a variable and
    then trying to populate the text boxes with the data in the variables

    Public Sub frmArt_Initialize()
    Dim bRow As Integer
    Dim bCol As Integer
    Dim valDesQ As Double 'This holds the value read from the spreadsheet
    Dim valDesTD As Double 'This holds the value read from the spreadsheet
    bRow = 1
    bCol = 1
    With Worksheets("Art Costing")
    valDesQ = .Cells(bRow + 9, bCol + 2).Value
    valDesTD = .Cells(bRow + 9, bCol + 1).Value
    End With
    '
    Me.intDesQ.Value = valDesQ 'This I hope would populate the text box
    Me.intDesTD.Value = valDesQ ' and so would this.
    End Sub


    The problem I have is that how do I connect the form load event to the form initialize
    event so that it fires before or while the form is loading and being shown?

    My form loads from a button which fires this macro

    Public Sub Form_Load_Art()

    frmArt.Show

    End Sub

    Whew, I hope this is clearer than my first post.

    Thanks

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Add data from excel into form on load (Excel/V

    A UserForm doesn't have a Load event; the Initialize event is the one to use if you want to preset some controls. If the code you posted doesn't populate the intDesQ and intDesTD text boxes, set a break point on the first line of the Initialize procedure. When you open the form, code execution will pause at that line. Execute the code step by step by pressing F8 repeatedly. Hover the mouse pointer over variable names to see their value. Do you see an unexpected result?

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Add data from excel into form on load (Excel/V

    Hans,

    I did set a break point on the first line of the Initialize procedure. However,
    it did not pause during execution. I tried a number of different things and
    can't get the value to appear in the form.

    I copied the initialize code renaming it to loadvalues into a separate code module
    and you can step through that and see that it does pick up the value from the
    spreadsheet and put it into the variable.

    I am attaching a zip of the spreadsheet so you can see for yourself. It works
    if we are only entering new values. The values entered into the first column in
    frame Hours. The values entered into the form populate the data area of the
    destination spreadsheet no problem.

    I want to be able to show what the values already there are, so that the artists
    entering their current hours can see what is already there and what the quoted hours
    were.

    The form opens from a button on the summary tab of the workbook.

    I am at a loss as to what to do next.

    Any other ideas?

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Add data from excel into form on load (Excel/V

    You declare the event procedure as

    Public Sub frmArt_Initialize()

    That is not correct, it MUST be named UserForm_Initialize, regardless of the actual name of the form:

    Private Sub UserForm_Initialize()

    Note 1: you have commented out an End With that is needed.
    Note 2: the easiest way to create an event procedure is:
    - Double click an empty part of the form.
    - This will create an UserForm_Click event procedure.
    - Select the Event dropdown list in the upper right corner of the module window.
    - Select Initialize from the list.
    - You can now delete the UserForm_Click procedure, unless you explicitly need it.

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Add data from excel into form on load (Excel/V

    Woo Hoo,

    This is me skipping and dancing around my office ...

    Not a pretty picture.

    Thanks SO much, now I'll be able to keep what little hair I have left.

    John

Posting Permissions

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