• UserForm (2002)

    Author
    Topic
    #452027

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #1114653

      Nathan

      Add this code to the command button:

      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
      

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

      • #1114658

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

        • #1114660

          Hi Again

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

          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
          

          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 grin

          • #1114663

            Thanks Jerry, I will test this tomorrow and post back. cheers

          • #1115467

            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 grin. I had saved and closed the file, and upon re-opening, my pc crashed. bwaaah . Ever since, that file is absolutely nowhere to be seen, notmyfault 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! compute bananas

            • #1115469

              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.

            • #1115472

              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.

    Viewing 0 reply threads
    Reply To: UserForm (2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: