This page is on under updatation. see again File download link available at end of this page Product add & Search userform. vba codes are follow
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "121"
'product add to invoice
Dim k As Long
For k = 1 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(k) = True Then
ActiveCell.Value = Me.ListBox1.Column(1)
ActiveCell.Offset(0, 2).Value = Me.ListBox1.Column(2)
ActiveCell.Offset(0, 6).Value = Me.ListBox1.Column(3)
End If
Next k
ActiveSheet.Protect "121"
Unload Me
End Sub
Private Sub CommandButton2_Click()
'add new item into Item Master
Dim sh As Worksheet
Set sh = Sheets("Item Master")
Dim i As Long
i = sh.Range("B" & Rows.Count).End(xlUp).Row + 1
If Me.TextBox2 = "" Or Me.TextBox3 = "" Or Me.TextBox4 = "" Or Me.TextBox5 = "" Or Me.TextBox6 = "" Then
MsgBox "Please filled all the fields."
Exit Sub
End If
sh.Cells(i, 1).Value = Me.TextBox2.Value
sh.Cells(i, 2).Value = Me.TextBox3.Value
sh.Cells(i, 3).Value = Me.TextBox4.Value
sh.Cells(i, 4).Value = Me.TextBox5.Value
sh.Cells(i, 5).Value = Me.TextBox6.Value
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox5 = ""
Me.TextBox6 = ""
MsgBox "Product has been added succesfully"
End Sub
Private Sub TextBox1_Change()
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Dim sh As Worksheet
Set sh = Sheets("Item Master")
Dim i As Long
Dim x As Long
Dim p As Long
Dim a As Integer
With Me.ListBox1
.Clear
'FOR LISTBOX HEADER
.AddItem "Product Code"
.List(.ListCount - 1, 1) = "Product Name"
.List(.ListCount - 1, 2) = "HSN Code"
.List(.ListCount - 1, 3) = "GST %"
.List(.ListCount - 1, 4) = "Rate"
.Selected(0) = True
End With
For i = 2 To sh.Range("B" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 2))
p = Me.TextBox1.TextLength
If Me.CheckBox1 = True Then a = 1
If Me.CheckBox2 = True Then a = 2
If LCase(Mid(sh.Cells(i, a), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 1)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 2)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 3)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 4)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 5)
End With
End If
Next x
Next i
'........ Remove duplicate item from listbox
Dim k As Long
Dim j As Long
With Me.ListBox1
For k = 0 To .ListCount - 1
For j = .ListCount - 1 To (k + 1) Step -1
If .List(j) = .List(k) Then
.RemoveItem j
End If
Next
Next
End With
End Sub
Private Sub UserForm_Initialize()
Me.CheckBox2.Value = True
End Sub
To get download link click the link and you will get download link on your email shortly. do not forget to give your feedback. It will inspaire me a lot. If you want to modify then simply unprotect the sheet with File Password is 121 and Place your trade name & others. https://forms.gle/VPKkrQGBxLrRDYu96
Excelent
ReplyDelete