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]

You can use the same form for entering or retrieving, but it must be very clear to the user what the form is doing. This can be done by changing the captions on the buttons, for instance and a textfield on the form.

As an example: when entering data button two can be used to clear the form (for instance the user notices he has already added the item). But it should then not try to delete a row in the database.
If the user has retrieved an item from the database (what you are asking for) then the delete button should clearly say it will delete item from the database (perhaps even with a warning, are you sure?)

I suggest there are two radio buttons on the sheet with which the user can select if he is adding or editing items.
You want to add another combobox to load the items in column C in. So this combobox could be visible only when in edit mode. And the button 2 caption and action can then also easily be modified depending on the mode.

By the way,

I have gone through your code and made it a bit clearer. Whenever you duplicate pieces of code, it is cleaner to put it in a sub and call the sub (by hte way, you can, but don't need to add 'Call' in front of the sub name). See below, the button action looks a lot neater.

Also, you had all kind of warnings if the buttons were pressed without the fields being completed. That is a negative approach (punishment, you stupid, you forgot something!). Better to not enable the buttons until the fields are filled out. For this end I have added the textbox_change subs. Here you could also add things like checking that only numbers have been entered. That's for the next step.

If you like this approach, let me know and I will help you with it next week.

Option Explicit

Private Sub CommandButton1_Click()
    
    StoreValues
    ClearForm
    Call Sort_Me

End Sub

Private Sub CommandButton2_Click()
    
    ClearForm

End Sub

Private Sub CommandButton3_Click()
    
    StoreValues
    Call Sort_Me
    Unload Me
    
End Sub
Private Sub CommandButton4_Click()
    
    Call Sort_Me
    Unload Me

End Sub

Private Sub ComboBox1_Change()
    TxtBxCheck
End Sub

Private Sub TextBox1_Change()
    TxtBxCheck
End Sub

Private Sub TextBox2_Change()
    TxtBxCheck
End Sub

Private Sub TextBox3_Change()
    TxtBxCheck
End Sub

Private Sub UserForm_Initialize()
    
    TextBox1.SetFocus
    With ComboBox1
        .AddItem "KG"
        .AddItem "Unit"
        .AddItem "Bunch"
        .AddItem "Punnet"
        .AddItem "Bag"
    End With
    TxtBxCheck

End Sub
Sub Sort_Me()
    
    With Worksheets("Sheet1").Range("$C$2").CurrentRegion
        .Sort Key1:=.Columns(3), Header:=xlYes, Order1:=xlAscending
    End With
        
End Sub

Private Sub TxtBxCheck()
'set the
    Dim bOnOff As Boolean
    
    bOnOff = Len(TextBox1) > 0 And Len(TextBox2) > 0 And Len(TextBox3) > 0 And ComboBox1.ListIndex > 0
    
    CommandButton1.Enabled = bOnOff
    CommandButton2.Enabled = bOnOff

End Sub

Private Sub ClearForm()
    Dim ctlBox As MSForms.Control
    
    For Each ctlBox In Me.Controls
        If TypeName(ctlBox) = "TextBox" Or TypeName(ctlBox) = "ComboBox" Then
            ctlBox.Value = ""
        End If
    Next ctlBox
'>> for small number of items the below is fine, but if the number increases, the above is far better
'>> also, you can refer to the current userform as 'Me'
'    UserForm2.TextBox1.Value = ""
'    UserForm2.TextBox2.Value = ""
'    UserForm2.TextBox3.Value = ""
'    UserForm2.ComboBox1.Value = ""

End Sub

Private Sub StoreValues()
    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

End Sub

 

[Trueblue862]

I like it, I never even knew that it was an option to disable the buttons, works so much better than the message box version that I had. The only thing I have changed is I have also added command button 3 to the list to be disabled until everything is filled out. It would be fantastic if you could make it so that text box 2 and 3 would only accept numbers.

There is one problem with the code you have done, when I select "KG" in the command box it doesn't activate the buttons, all the other units work.

I am very much an amateur at all this and I have simply been piecing together bits of code I have found on the internet and adjusting it as required, most of the time I have been able to get it to work , but I have had a few which I have gotten stuck on, and this user form to edit the table is one of them.

I have created a separate user form for the edit/delete mode, activated off a separate button on the worksheet, it contains a second combo box which I was intending to use as a way to select which item to edit, other than this it is identical to the original. If there is a better way to achieve this I'm open to all suggestions.

Thank you for your help so far, it would be fantastic if you could help me get this to work.

 

[Trueblue862]

Correction, I meant to say that I had changed command button 2 in your code to command button 3 because command button 2 is to clear the form and needs to be activated all the time when the form is in use.

 

[sijpie]

to correct for the kg:

Private Sub TxtBxCheck()
'set the
    Dim bOnOff As Boolean
    
    bOnOff = Len(TextBox1) > 0 And Len(TextBox2) > 0 And Len(TextBox3) > 0 And ComboBox1.ListIndex >= 0
    
    CommandButton1.Enabled = bOnOff
'    CommandButton2.Enabled = bOnOff
    CommandButton3.Enabled = bOnOff

End Sub

 

[Trueblue862]

Thank you, I’ll give it a try tomorrow after work.

 

[Trueblue862]

Thank you it works.

 

[sijpie]

I am working on the edit form. Will post it next week.

 

[Trueblue862]

Thank you very much, I really appreciate all the help I have received from this forum.

 

[sijpie]

Hi Trueblue,

The working code for a form is posted below. Since you are trying to learn and improve your programming, I will give you some tips, which I have also applied in the code below. Feel free to do the same for the entry form code above.

First of all: when you are writing code, creating a userform and such, what you are doing is well in your head. You understand your code. However, even after a few months, a lot will be forgotten. So it is important to use names that are descriptive and put lots of comments in the code: what does a function do? what does this little bit of code achieve?

When you create a form the VBE (virtual basic editor) will assign automatic names to each of the controls that you add to the form. But you can modify these names to something meaningful. CommandButton1 can become btnSave. So when you see btnSave in your code you know what the button does! (or is supposed to do ). The best thing is to rename the control immediatly, then when you double-click on the control, the VBE will add the correct name in the code. How? In the properties window, bottom left, you can see all the properties belonging to a control and to the userform itself.

Here you can see which item you are looking at (top red arrow, below), and its properties. Change the name in the (name) field (bottom red arrow).

You can see that I also rename userforms to something meaningful. In this case ufEdit. uf means the object is a userform. Compared to Userform2 in the list above this is far more understandable. So if in your code you write: ufEdit.Show , you immediately understand which userform is being shown.

By using these little prefixes consistently also for all your variables, you have far better understanding of what the code is doing, and less chance of bugs. I also always use at least one capital letter when Dimming the variable. When later typing the variable, I don't use the capital. If I typed it correctly, the VBE will capitalise it for me: no capital means I made a typo!

Option Explicit
 
Dim mvItemsTbl As Variant, mvUnits As Variant   'declare as module variable, so can be used by all subroutines. _
                             initiated in Userform_Initialize


Private Sub btnSave_Click()
'Save changes button pressed
    StoreValues
    ClearForm
    Call Sort_Me
    LoadCombos       'to reload the now changed database into the array

End Sub

Private Sub btnDel_Click()
'Remove button pressed
    Dim mbrYN As VbMsgBoxResult
    
    'check if user really wants to remove
    mbrYN = MsgBox(prompt:="Are you sure you want to delete all details for " & vbCrLf & _
                         cbxItem.Value & "from the database?", _
                 Buttons:=vbExclamation + vbYesNo, _
                 Title:="Caution, deleting items from the data base!")
    If mbrYN = vbNo Then Exit Sub   'Ah, no. didn't want to remove/delete
    'else delete the row
    DeleteRow
    ClearForm
    LoadCombos       'to reload the now changed database into the array
    'no need to sort
End Sub

Private Sub btnCancel_Click()
' cancel button pressed. Just close
    Unload Me
    
End Sub

Private Sub cbxItem_Change()
'Item selection combo box changed. Runs when user selects item

    Dim iSelected As Integer, j As Integer
    
    'check if item selected (user could have cleared entry), and load details
    If Len(cbxItem.Value) = 0 Then Exit Sub
    iSelected = cbxItem.ListIndex + 2 'listindex starts at 0 for 1st item, mvItemsTbl starts at 2 (1 is the header)
    'load details into textboxes and unit combo
    tbxDescr = mvItemsTbl(iSelected, 3)
    tbxCostPr = mvItemsTbl(iSelected, 1)
    tbxUnitP = mvItemsTbl(iSelected, 2)
    For j = 0 To cbxUnits.ListCount - 1
        'find the unit used in the combo
        If LCase(cbxUnits.List(j)) = LCase(mvUnits(iSelected, 1)) Then
            cbxUnits.ListIndex = j
            Exit For
        End If
    Next j
End Sub

Private Sub cbxUnits_Change()
'Units combo changed. Check if status of buttons needs to change
    TxtBxCheck
End Sub

Private Sub tbxDescr_Change()
'Description textbox changed. Check if status of buttons needs to change
    TxtBxCheck
End Sub

Private Sub tbxCostPr_Change()
'cost price textbox changed. Check if status of buttons needs to change
    TxtBxCheck
End Sub

Private Sub tbxunitp_Change()
'Units textbox changed. Check if status of buttons needs to change
    TxtBxCheck
End Sub





Private Sub UserForm_Activate()
'Actions to perform on activating userform
    'load unit combobox
    With cbxUnits
        .Clear
        .AddItem "KG"
        .AddItem "Unit"
        .AddItem "Bunch"
        .AddItem "Punnet"
        .AddItem "Bag"
    End With
    'load item combobox
    LoadCombos
    'set button status
    TxtBxCheck

End Sub

Private Sub UserForm_Initialize()

End Sub


Sub Sort_Me()
'Sort database on item name.
'<<<< this sub really belongs in a module, since it is identical _
      to the one in the add userform and it does not operate on the userform itself
    With Worksheets("Sheet1").Range("$C$2").CurrentRegion
        .Sort Key1:=.Columns(3), Header:=xlYes, Order1:=xlAscending
    End With
        
End Sub

Private Sub TxtBxCheck()
'set the button status. If one of the required fields is empty buttons are disabled
    Dim bOnOff As Boolean
    
    bOnOff = Len(tbxDescr) > 0 And Len(tbxCostPr) > 0 And Len(tbxUnitP) > 0 And cbxUnits.ListIndex >= 0
    
    btnSave.Enabled = bOnOff
    btnDel.Enabled = bOnOff

End Sub

Private Sub ClearForm()
'loop through the controls and set to empty
    Dim ctlBox As MSForms.Control
    
    For Each ctlBox In Me.Controls
        If TypeName(ctlBox) = "TextBox" Or TypeName(ctlBox) = "ComboBox" Then
            ctlBox.Value = ""
        End If
    Next ctlBox

End Sub

Private Sub StoreValues()
'store the values in the worksheet, overwriting current
    Dim iRw As Integer
    Dim wsWS As Worksheet
    
    Set wsWS = Worksheets("Sheet1")
    iRw = cbxItem.ListIndex + 2 'listindex starts at 0, plus a header row, so +2

    wsWS.Cells(iRw, 3).Value = Me.tbxDescr.Value
    wsWS.Cells(iRw, 1).Value = CDbl(Me.tbxCostPr.Value)
    wsWS.Cells(iRw, 2).Value = CDbl(Me.tbxUnitP.Value)
    wsWS.Cells(iRw, 15).Value = Me.cbxUnits.Value

End Sub

Private Sub DeleteRow()
'delete selected item row from database
    Dim iRw As Integer
    Dim wsWS As Worksheet
    
    Set wsWS = Worksheets("Sheet1")
    iRw = cbxItem.ListIndex + 2 'listindex starts at 0, plus a header row, so +2

    wsWS.Cells(iRw, 3).EntireRow.Delete

End Sub

Private Sub LoadCombos()
'refresh the arrays. load the item combo. This needs to be done at _
 start and after every edit or sort
    Dim lR As Long
    
    'load columns A:C  and O into an arrays for fast processing
    With Sheets("Sheet1")
        lR = .Range("A1").CurrentRegion.Rows.Count
        mvItemsTbl = .Range("A1:C" & lR).Value
        mvUnits = .Range("O1:O" & lR).Value
    End With
    
    relaod combo
    With cbxItem
        .Clear
        For lR = 2 To UBound(mvItemsTbl, 1) 'skip the header row
            .AddItem mvItemsTbl(lR, 3)
        Next lR
        .SetFocus
    End With

End Sub

Read my handy guide (see link below) for more tips