Loading New Userform According To Clicked Listbox Item

March 17, 2017 Leave a comment

The second userform is shown when the listbox item is double-clicked. Text boxes are filled according to values of double-clicked item.

Codes that supply the loading of the userform and the filling of the textboxes when double-clicking on the listbox:

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Load UserForm2

UserForm2.TextBox1 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 0)

UserForm2.TextBox2 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 1)

UserForm2.TextBox3 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 2)

UserForm2.TextBox4 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 3)

UserForm2.TextBox5 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 4)

UserForm2.TextBox6 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 5)

UserForm2.TextBox7 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 6)

UserForm2.TextBox8 = VBA.Format(UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 7), “#,##.00”)

UserForm2.TextBox9 = VBA.Format(UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 8), “dd.mm.yyyy”)

UserForm2.TextBox10 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 0)

Unload UserForm1

UserForm2.Show

End Sub

Download Example File

Creating Invoice & Entering Data Quickly Through Userforms

March 2, 2017 1 comment

In this invoice template ,the userforms that contains textbox,listbox and buttons are used to enter quickly clients data and products data.

  • Ago,requested data can be searched in lists through the text boxes .
  • Then, when the list item is double-clicked or when the enter key is pressed, the item’s data is entered in the sheet.
  • Also, flashing cell feature was used in template to remind entering the product quantity . Used codes for this:

Public RunWhen As Double

Sub StartBlink()
Dim cell As Range
For Each cell In Sheets(“Invoice”).Range(“B21:B35”)
If cell <> “” And cell.Offset(0, 1) = “” Then
If cell.Offset(0, 1).Interior.ColorIndex = 3 Then ‘ Red Text
cell.Offset(0, 1).Interior.ColorIndex = 2 ‘ White Text
Else
cell.Offset(0, 1).Interior.ColorIndex = 3 ‘ Red Text
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , True
Else
cell.Offset(0, 1).Interior.ColorIndex = xlColorIndexAutomatic
End If
Next
End Sub

Sub StopBlink()
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , False
End Sub

  • The used formulas in worksheet are protected by the following codes :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$G$21” Then
Target.Formula = “=E21*F21”
ElseIf Target.Address = “$G$22” Then
Target.Formula = “=E22*F22”
ElseIf Target.Address = “$G$23” Then
Target.Formula = “=E23*F23”

….

Target.Formula = “=E34*F34”
ElseIf Target.Address = “$G$35” Then
Target.Formula = “=E35*F35”
ElseIf Target.Address = “$G$36” Then
Target.Formula = “=SUM(G21:G35)”
ElseIf Target.Address = “$G$41” Then
Target.Formula = “=SUM(G36:G39)”
End If

End Sub

  • The created invoice can be copied to selected record sheet.

Download Example File