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

    Update a worksheet from a UserForm (97/2k)

    I have a userform containing seven textboxes into which customer details are input. I wish for these details to add to the bottom of a list on a worksheet on clicking a command button. The userform would now show with the text boxes empty, ready for the next customer details and so on......
    I thought an efficient way would be to create a collection containing the text box values as items which could then be referenced through code. The worksheet could then be updated with these values.
    Would this be an efficient method or can anyone advise further.
    BTW. The help section for Excel VBA is impenetrable when it comes to Collections.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: Update a worksheet from a UserForm (97/2k)

    In this case, I don't think a collection has advantages. Perhaps an array will be easier:

    Dim arrValues(1 To 7)
    Dim rng As Range
    Dim i As Integer
    For i = 1 To 7
    arrValues(i) = Me.Controls("Text" & i)
    Next i
    Set rng = Range("A1:G1")
    rng = arrValues
    Set rng = Nothing

    Note: do you know about Data/Form...? It seems to be a built-in way of doing what you want...

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

    Re: Update a worksheet from a UserForm (97/2k)

    Hi Hans,
    I had a premonition that you would answer and thanks for doing so.
    I am aware of Data/Form but the problem is that the details to be added are not contiguous. Then there is the formatting issue.
    Would you kindly help me further?
    I am determining where the details are to be added by using:

    x = ActiveCell.CurrentRegion.Rows.Count as the Row number e.g. Range("A"&x+1).Value= etc
    What would be the syntax for this sort of range:
    Range("A1:G1") using x as the row number?

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: Update a worksheet from a UserForm (97/2k)

    Say that your data start in A1, and there are no gaps in column A. You can use code like this:

    Dim rng As Range
    Set rng = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Resize(1, 7)

    End(xlDown) returns the last cell in A, Offset(1,0) returns the cell 1 down, so the first empty cell in A, and Resize(1, 7) returns an area 1 row high and 7 columns wide.

    or

    Dim x As Long
    Dim rng As Range
    x = ActiveSheet.Range("A1").End(xlDown).Row + 1
    Set rng = ActiveSheet.Range(ActiveSheet.Cells(x, 1), ActiveSheet.Cells(x, 7))

    If there are gaps in column A, you can find the last filled cell in that column by using Range("A65535").End(xlUp) instead of Range("A1").End(xlDown).

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

    Re: Update a worksheet from a UserForm (97/2k)

    Perfect, Hans. Thanks for your time and expertise.

    Cheers

    Rob
    <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
  •