In this tutorial I will explain how you can create an graphic interface like the one you have on the right. Of course it's up to you to design the boxes, color them, move them as you like as soon as you understand the basics of how you can do this.
Firstly you need to activate the developer tool tab, setting which you can learn from this article. What's different from the last article is that you need to insert an userform object, not a module. You can follow the image on the right on how to do this.
Next step is to apply the styles, object positioning (boxes, buttons, etc). As you can see I used 8 textboxes, 1 dropdown combo box and 1 button for inserting the data from the fields.
Next step is to apply the styles, object positioning (boxes, buttons, etc). As you can see I used 8 textboxes, 1 dropdown combo box and 1 button for inserting the data from the fields.
Double click on the button and insert the code below
Code:
Private Sub CommandButton1_Click()
'We declare the variables
Dim lrCD As Long, lrPD As Long, lrS As Long
Dim rasp
Dim intLastRow
Dim d
intLastRow
= Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row 'we need to know where is the last cell ocuppied with information so that when we insert data from the userform we won't be overwriting existing data.
'All data will be submited to Sheet1
If Sheets("Sheet1").Range("A:A").Find(CLng(TextBox1.Value), LookIn:=xlValues) Then
MsgBox ("Request number is already registered!") 'A condition so that our first field, the request name is unique.
Else
dd:
Cells(intLastRow + 1, 9) = "Request registered"
'We block the user from leaving fields empty upon submiting
'We block the user from leaving fields empty upon submiting
If TextBox2.Text = "" Then
MsgBox ("Field is mandatory")
End If
If TextBox3.Text = "" Then
MsgBox ("Field is mandatory")
End If
If TextBox4.Text = "" Then
MsgBox ("Field is mandatory")
End If
If TextBox5.Text = "" Then
MsgBox ("Field is mandatory")
End If
If TextBox6.Text = "" Then
MsgBox ("Field is mandatory")
End If
If TextBox7.Text = "" Then
MsgBox ("Field is mandatory")
End If
If TextBox8.Text = "" Then
MsgBox ("Field is mandatory")
End If
If ComboBox1.ListIndex = -1 Then
MsgBox ("Selection is mandatory")
End If
lrCD = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Cells(lrCD
+ 1, "A").Value = TextBox1.Text 'In sheet1 we insert the value of the textbox in userform.
Sheets("Sheet1").Cells(lrCD + 1, "B").Value = TextBox2.Text
Sheets("Sheet1").Cells(lrCD + 1, "C").Value = TextBox3.Text
Sheets("Sheet1").Cells(lrCD + 1, "D").Value = TextBox4.Text
Sheets("Sheet1").Cells(lrCD + 1, "E").Value = TextBox5.Text
Sheets("Sheet1").Cells(lrCD + 1, "F").Value = TextBox6.Text
Sheets("Sheet1").Cells(lrCD + 1, "G").Value = TextBox7.Text
Sheets("Sheet1").Cells(lrCD + 1, "H").Value = TextBox8.Text
Sheets("Sheet1").Cells(lrCD + 1, "K").Value = ComboBox1.Text
'We need to empty the fields after submitting them to the sheet. We don t want after submit to have data in our fields.
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl
End If
Exit Sub
ErrHand:
ErrorHandler:
Select Case Err.Number
'Common error #1: path or name is incorrect
Case 1004
Range("D10:E11").ClearContents
Application.ScreenUpdating = True
MsgBox ("The file could not be found in the path specified")
Exit Sub
'Common error #2: The text specified does not exist
Case 9, 91
GoTo dd
Exit Sub
End Select
End Sub
Private Sub UserForm_Initialize()
'We add elements for the listbox
With Me.ComboBox1
.Clear 'Clear elements if they were any before
.AddItem "Standard"
.AddItem "Nestandard"
.AddItem "Simplu"
.AddItem "Complex"
End With
End Sub
 |
| Selecting a textbox to verify the name of the variable |
Comments
Post a Comment