Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User form to create ordered list (English/Microsoft Office/2003)

    I need to create a user form through VBA that allows the user to make an ordered list.

    I need to be able to input

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

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Welcome to Woody's Lounge!

    You only mention Microsoft Office in the subject. Am I correct in assuming that you want to do this in Excel?

  3. #3
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Yes. You are right to assume that this project is being done in Excel.

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

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Do you have a particular reason for wanting to create the userform and the code behind it in VBA, instead of designing the form and writing the code directly?

  5. #5
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>

    Yes. It is a school assignment that I am working on and that is the specifications for the project.
    This is the the web page that tells my assignment. http://scienceandmathacademy.com/academics...ListCreator.pdf

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

    Re: User form to create ordered list (English/Microsoft Office/2003)

    It's very honest of you to admit that it's a school assignment!

    If I read the assignment correctly, the intention is that you create a userform and the corresponding code in the Visual Basic Editor, not that you write code to create the userform and code.

    To get an idea of the code you need, try the following:
    1) Experiment interactively.
    - Enter a start value in a cell.
    - Select the cell and some cells below it.
    - Select Edit | Fill | Series...
    - Enter a Step Value and a Stop Value, then click OK.
    2) When step 1) works to your satisfaction:
    - Select Tools | Macro | Record new macro...
    - Specify a name, then click OK.
    - Repeat the above steps.
    - When done, stop recording.
    - Activate the Visual Basic Editor (Alt+F11).
    - Open the module that has been created.
    - Study the generated code. If you want help, click in a word and press F1.
    - The essential keyword is DataSeries.
    3) Now try to replace the fixed values in the macro (for Step and Stop) by references to the controls (text boxes) on your userform.

    Good luck!

  7. #7
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Thank you. =)

  8. #8
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Ok. So I've come up with this code.

    Private Sub btnCreateList_Click()

    Dim StartNum As Long
    Dim EndNum As Long
    Dim Increment As Long
    Dim StartRng As Range
    Dim Ndx As Long

    Set StartRng = ActiveSheet.Range(Me.tbxStartCell.Text)
    StartNum = CLng(Me.tbxStartNum.Text)
    EndNum = CLng(Me.tbxEndNum.Text)
    Increment = CLng(Me.tbxIncrement.Text)

    For Ndx = StartNum To EndNum Step Increment
    StartRng.Value = Ndx
    Set StartRng = StartRng.Offset(1, 0)
    Next Ndx



    End Sub


    It works for all the basics. The only problem is say I have 20 points ranging from A1-A21 and I want to put 15 different points ranging from A1-A16. But then I am left with the points from A17-A21 of the first set of points. What do I need to change in my code so that those points from the previous list don't appear or are erased?

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

    Re: User form to create ordered list (English/Microsoft Office/2003)

    If it is OK to erase all cells below the start cell before filling the cells with the new series, you can use

    Set StartRng = ActiveSheet.Range(Me.tbxStartCell.Text)
    ActiveSheet.Range(StartRng.Offset(1, 0), StartRng.End(xlDown)).ClearContents
    StartNum = CLng(Me.tbxStartNum.Text)
    ...

  10. #10
    New Lounger
    Join Date
    Mar 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form to create ordered list (English/Microsoft Office/2003)

    Thank you very much. That works great!

Posting Permissions

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