Searchable userform to update a table

[Trueblue862]

Hi, I'm trying to create a userform to search, update and delete Items from a database, I'm not really sure on how to achieve this. I'm not very good with VBA but I'm slowly learning. I have so far managed to create a form to enter data into this database, which works well, but I don't know how to change this code to then search and update the database. I have attached the first few rows of the database.

The userform I am wanting to use to edit the data is the same as the one I'm using to enter the data, with the exception that I'm wanting to use Combobox 2 to search the Product names in the database column C, and I want command button 2 to not only clear the form, but I want it to delete the entry in the database and sort the database alphabetically by product name in Column C.

I have found a few versions online, but I can't figure out how to make any of them work in my application.

Any help with this would be greatly appreciated.

Ordering Sheet ver 2 - lets see if this works.xlsm
	A	B	C	D		F		H		J		L		N	O	P
1	Cost Price	Unit price	Products	Friday		Saturday		Sunday		Nosh		Goodstart		Total for Ordering	KG/Units	Weekly Cost Per Item
2	6.00	7.00	700g eggs												unit
3	2.00	4.20	Apples Kg												KG
4	3.00	1.00	Avocadoes												unit
5	4.00	2.90	baby spinach												bag
Sheet1

Cell Formulas
Range	Formula
N2:N5	N2	=SUM(E2,G2,I2,K2,M2)
P2:P5	P2	=A2*N2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=MOD(ROW(),2)=0	text	NO
B2:C56	Expression	=MOD(ROW(),2)=0	text	NO
B1:O1,B324:O1048576,D2:O2,P:P,D3:M56,B57:M323,N3:O323	Expression	=MOD(ROW(),2)=0	text	NO

This is the code I use to input into the database.

Private Sub CommandButton1_Click()
If TextBox1.Value = "" Then
     MsgBox "You must add product description", vbCritical
    Exit Sub
 End If
 If TextBox2.Value = "" Then
    MsgBox "You must add cost price", vbCritical
    Exit Sub
 End If
 If TextBox3.Value = "" Then
    MsgBox "You must add selling price", vbCritical
    Exit Sub
 End If
  If ComboBox1.Value = "" Then
    MsgBox "You must select KG/UNIT", vbCritical
    Exit Sub
 End If
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
rw = ws.Cells.Find(what:="*", searchorder:=xlRows, Searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(rw, 3).Value = Me.TextBox1.Value
ws.Cells(rw, 1).Value = Me.TextBox2.Value
ws.Cells(rw, 2).Value = Me.TextBox3.Value
ws.Cells(rw, 15).Value = Me.ComboBox1.Value
UserForm2.TextBox1.Value = ""
UserForm2.TextBox2.Value = ""
UserForm2.TextBox3.Value = ""
UserForm2.ComboBox1.Value = ""
Call Sort_Me
End Sub

Private Sub CommandButton2_Click()
UserForm2.TextBox1.Value = ""
UserForm2.TextBox2.Value = ""
UserForm2.TextBox3.Value = ""
UserForm2.ComboBox1.Value = ""
End Sub

Private Sub CommandButton3_Click()
If TextBox1.Value = "" Then
     MsgBox "You must add product description", vbCritical
    Exit Sub
 End If
 If TextBox2.Value = "" Then
    MsgBox "You must add cost price", vbCritical
    Exit Sub
 End If
 If TextBox3.Value = "" Then
    MsgBox "You must add selling price", vbCritical
    Exit Sub
 End If
  If ComboBox1.Value = "" Then
    MsgBox "You must select KG/UNIT", vbCritical
    Exit Sub
 End If
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
rw = ws.Cells.Find(what:="*", searchorder:=xlRows, Searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(rw, 3).Value = Me.TextBox1.Value
ws.Cells(rw, 1).Value = Me.TextBox2.Value
ws.Cells(rw, 2).Value = Me.TextBox3.Value
ws.Cells(rw, 15).Value = Me.ComboBox1.Value
Call Sort_Me
Unload Me
End Sub
Private Sub CommandButton4_Click()
Call Sort_Me
Unload Me
End Sub

Private Sub Label4_Click()

End Sub

Private Sub UserForm_Initialize()
TextBox1.SetFocus
With ComboBox1
    .AddItem "KG"
    .AddItem "Unit"
    .AddItem "Bunch"
    .AddItem "Punnet"
    .AddItem "Bag"
End With
End Sub
Sub Sort_Me()
With Worksheets("Sheet1").Range("$C$2").CurrentRegion
    .Sort Key1:=.Columns(3), Header:=xlYes, Order1:=xlAscending
End With
        
End Sub

 

[sijpie]

I forgot to mention one thing in the above: At the top of the code I dim a few variabales which can be used throughout this module by all the subs. So called variables with a module scope. I start these variables with an extra m in front for module. So mvUnits is a variant (v) with a module scope (m). If it were a variable with a global scope (declared in a standard module, available everywhere) then I would declare it as gvItemDB, a variant with global scope.

 

[Trueblue862]

sijpie,
Thanks a lot mate, it works a treat. Also thank you very much for all those tips I will definitely put that into practice in the future. Simple things like changing the names of the command buttons and textboxes are something I never even thought about, but makes so much sense once I'm told about it. I really appreciate you taking the time to help me with this, thank you very much.