Results 1 to 8 of 8

Thread: UserForm (2002)

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UserForm (2002)

    Ok, I'm finally taking the plunge and attempting to create a UserForm, but I am unsure of the coding.
    I want the userform to open with a specific workbook and I want both the users name and supervisors name to be entered prior to continuing. Preferably, where this cannot be bypassed in any way, where each input box should contain both first and surname (Is it possible to only continue if say a minimum of 7 characters were entered, including a compulsory space, so the bare minimum would be 'Ted Fox' for example). Then assign the names to specific cells. I have attached what I have thus far.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm (2002)

    Nathan

    Add this code to the command button:
    <pre>Private Sub CommandButton1_Click()
    Dim intUser As Integer
    Dim intSup As Integer


    intUser = Range("A" & Rows.Count).End(xlUp).Row
    intSup = Range("B" & Rows.Count).End(xlUp).Row

    Cells(intUser + 1, 1).Value = TextBox1.Value
    Cells(intUser + 1, 2).Value = TextBox2.Value


    End Sub
    </pre>


    I am making the assumption you want to add the details of the user and the supervisor in columns A and B
    Jerry

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm (2002)

    Thanks Jerry, There are specific cells within the workbook that would require these inputs? Can I use formula to pick them up: =intuser / =intsup ??

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm (2002)

    Hi Again

    I have been working on this as you were typing to add validation and I will adapt it to answer your question:

    <pre>Private Sub CommandButton1_Click()

    If Len(TextBox1) < 7 Then
    MsgBox "The User Name must be at least 7 letters long"
    TextBox1.Value = ""
    TextBox2.Value = ""
    Exit Sub
    End If

    If Len(TextBox2) < 7 Then
    MsgBox "The Supervisor's Name must be at least 7 letters long"
    TextBox1.Value = ""
    TextBox2.Value = ""
    Exit Sub
    End If

    If InStr(1, TextBox1, " ", 1) = 0 Then

    MsgBox "Please enter a surname in the User box"

    End If

    If InStr(1, TextBox2, " ", 1) = 0 Then

    MsgBox "Please enter a surname in the supervisor box"

    End If



    Cells(1, 1).Value = TextBox1.Value
    Cells(1, 2).Value = TextBox2.Value


    End Sub
    </pre>



    If you have specific cells to add the values to you can just adjust

    Cells(1, 1).Value = TextBox1.Value
    Cells(1, 2).Value = TextBox2.Value


    using the syntax

    Cells([Row Value], [Column Value])

    I hope that is clearer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm (2002)

    Thanks Jerry, I will test this tomorrow and post back. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm (2002)

    Jerry,

    Thanks vm for your help here, using your code and a bit of googling, testing and playin about, I managed to build my first userform and incorporate into a live workbook. I was chuffed <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I had saved and closed the file, and upon re-opening, my pc crashed. <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> . Ever since, that file is absolutely nowhere to be seen, <img src=/S/notmyfault.gif border=0 alt=notmyfault width=15 height=15> disappeared off the face of the earth!! Tis not a problem, as rebuilding just provides more experience.

    Where does a file just disappear to?, anyway, am sick and tired of 'the blue screen of death', so am gonna treat myself to a new laptop! <img src=/S/compute.gif border=0 alt=compute width=40 height=20> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

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

    Re: UserForm (2002)

    A 'blue screen of death' should be rare on recent versions of Windows, so if you get one frequently, there's something seriously wrong.

    When Excel crashes, it should offer to restore the file next time you start the program. But if Windows crashes, Excel may not have had the time to create the files necessary for auto-recovery.

    Problems like these emphasize the importance of creating backups. You could, for example, make a copy of the workbook on a USB stick before editing it.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm (2002)

    The blue screen of death has become my soulmate the last few months, that and his brother Freeze!! I'm running windows XP and I think that is where the major issue is. Once I have my new laptop, I plan to strip my desktop back to it's bare bones and keep for a secondary pc. I've investigated all manner of different things, but never to a resolve. It may be a hardware issue, but I've never managed to pinpoint it.

    Added later: Where did the rest of my text go?, aah s0d it, I was probably babbling anyway.

Posting Permissions

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