Excel UserForm with Text Input Box for "Projectnummer"

This document outlines how to create an Excel UserForm with a text input box labeled "Projectnummer" and the associated Excel formula to manage user input.

Steps to Create UserForm

1. Open the VBA Editor:

   • Press ALT + F11 to open the Visual Basic for Applications (VBA) editor in Excel.

2. Insert a UserForm:

   • Right-click on any of the items in the Project Explorer.
   • Select Insert > UserForm.

3. Add a Text Box:

   • From the Toolbox, select the Text Box control and draw it on the UserForm.
   • Set the Name property of the Text Box to txtProjectnummer.

4. Add a Label:

   • From the Toolbox, select the Label control and draw it near the Text Box.
   • Set the Caption property to "Projectnummer".

5. Add a Command Button:

   • From the Toolbox, select the Command Button control and place it on the UserForm.
   • Set the Caption property to "Submit" and Name to btnSubmit.

VBA Code for UserForm

The following VBA code handles the input from the text box and places it into a designated cell in the Excel sheet when the "Submit" button is clicked.

Private Sub btnSubmit_Click()
    Dim projectNumber As String
    projectNumber = Me.txtProjectnummer.Value
    
    ' Assuming you want to place the Projectnummer in cell A1
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = projectNumber
    
    ' Clear the text box after submission
    Me.txtProjectnummer.Value = ""
    
    ' Optional: Close the form after submission
    ' Un-comment the line below if you wish to close the form
    ' Unload Me
End Sub

Explanation of the Code

• Declaring Variables:

  • Dim projectNumber As String: This line declares a variable to store the value entered in the text box.

• Getting User Input:

  • The line projectNumber = Me.txtProjectnummer.Value assigns the textbox value to the variable.

• Placing Value in a Cell:

  • ThisWorkbook.Sheets("Sheet1").Range("A1").Value = projectNumber takes the user input and places it in cell A1 of "Sheet1".

• Clearing the Input:

  • Me.txtProjectnummer.Value = "" clears the text box after submission.

• Optional Form Closure:

  • The commented line Unload Me can be un-commented to close the form automatically after submission.

Practical Example

1. User Operation: When the UserForm opens, the user types a project number (e.g., "PN123").
2. Submission: Upon clicking the "Submit" button:
   • The entered project number "PN123" is stored in cell A1 of "Sheet1".
   • The text box is cleared for new input.

Conclusion

By utilizing the above VBA code and following the creation steps, you can efficiently manage user input for "Projectnummer" via an Excel UserForm. For further learning on Excel and VBA, consider exploring courses available on the Enterprise DNA Platform to enhance your skills.