Excel VBA UserForm not working. Need help pls!

[dos]

Hi all.

I am currently learning Excel VBA, so class myself as a beginner.

I managed to create a UserForm that can add food products and i also added a Search section that should display any product(s) when i enter any keyword of the Item Name. For example, i have a record for Red Leicester Cheese 100g. If i search for only the word "Red" or only the word "Cheese" then all records that have the word Red or Cheese should appear in the Search results box. I also saw on YouTube that as i type the keyword, then real-time search results are displayed. I would like to incorporate this into my UserForm.

I have tried many times to get my UserForm to work, but i am struggling, as there are several problems with it, which i have listed below. Please can anyone help?

1. I added code to the Create Worksheet button and it froze my computer, so i removed the code, but just left the button. Is there code that can get this button to add a new worksheet and also list it in the select sheet combo box?
2. I cannot get the Combo box (Priority) to list its value.
3. The date does not automatically populate.
4. Data is not aligned to its respective column in any of the three worksheets - Dairy, Bread, Drinks. I suspect this is because of the Priority combo box problem.
5. Finally, as explained in the beginning, is there code for the Search section, which can list data in the large search box exactly like it is in the excel file, just by searching on any keyword?

Of course if you have any suggestions to improve the look/feel of the UserForm, please do let me know.

I have attached my excel sample file.

Any help would be greatly appreciated.

Thank you in advance.

Dos

[Paul_Hossler]

This might move you a little farther in your quest

I renamed some controls for readability

You had two initialize subs - I combined them

Made some other tweaks (some just because it's my style)

Added "Add WS" code - seems to work (no error checking). SInce you can add worksheets, it'd be better I think to read the names into the combobox, instead of hard coding

Option Explicit
Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    
    Me.txtDate.Text = Format(Now(), "mm/dd/yyyy")
        
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "MasterSheet" Then cboxSheet.AddItem ws.Name
    Next
    
    With cboxPriority
        .AddItem "P1"
        .AddItem "P2"
        .AddItem "P3"
    End With
End Sub




Private Sub btnAddData_Click()
    Dim lastrow As Long
    
    If cboxSheet.Value = "" Then Exit Sub
    With Worksheets(cboxSheet.Value)
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(lastrow + 1, 1).Value = txtDate.Value 'date
        .Cells(lastrow + 1, 2).Value = TextBox2.Value 'item name
        .Cells(lastrow + 1, 3).Value = TextBox3.Value 'section
        .Cells(lastrow + 1, 4).Value = TextBox4.Value 'person responsible
        .Cells(lastrow + 1, 5).Value = TextBox5.Value 'item name
        .Cells(lastrow + 1, 6).Value = TextBox6.Value 'customer id
        .Cells(lastrow + 1, 7).Value = TextBox7.Value 'batch
        .Cells(lastrow + 1, 8).Value = TextBox8.Value 'comments
    End With
    
    MsgBox ("Data is added successfully")
    
End Sub


Private Sub btnClearForm_Click()
    txtDate.Value = vbNullString
    '   etc.
End Sub


Private Sub btnCreateWorksheet_Click()
    If Len(cboxSheet.Value) = 0 Then Exit Sub
    Worksheets.Add.Name = cboxSheet.Value
    
    'to get added sheet to show
    cboxSheet.Clear
    Call UserForm_Initialize    '
End Sub


Private Sub btnExit_Click()
    Me.Hide
    Unload Me
End Sub


Private Sub btnSearch_Click()
    MsgBox "Search Button"
End Sub

[dos]

Hi Paul.


Thank you for cleaning up the code. I knew it was in a mess, but didn't know how to clean it without breaking something.


Do you know why the following columns are not displaying their respective data - Priority/Customer iD/Batch Number/Comments?


For example, in the Priority column, it should display data P1, P2 or P3, but none of these are showing. Instead the Priority column is showing the Customer iD (CS3) data. And the Customer iD column is showing the Batch Number (B3) data. And finally the Batch Number column is showing Comments data, which should be displayed in the Comments column. So it seems because the data in the Priority column is missing, the other columns data is misaligned.


I really hope i have explained this correctly?


Thank you for your great support and advice Paul.


Dos

[Paul_Hossler]

You weren't putting the Priority into column 6, so the last 3 went into 6,7,8 instead of 7,8,9

Private Sub btnAddData_Click()
    Dim lastrow As Long
    
    If cboxSheet.Value = "" Then Exit Sub
    With Worksheets(cboxSheet.Value)
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(lastrow + 1, 1).Value = txtDate.Value 'date
        .Cells(lastrow + 1, 2).Value = txtItem.Value 'item name
        .Cells(lastrow + 1, 3).Value = txtSection.Value 'section
        .Cells(lastrow + 1, 4).Value = txtPerson.Value 'person responsible
        .Cells(lastrow + 1, 5).Value = txtItemName.Value 'item name
        
        .Cells(lastrow + 1, 6).Value = cboxPriority.Value 'priority '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
        .Cells(lastrow + 1, 7).Value = txtCustomer.Value 'customer id
        .Cells(lastrow + 1, 8).Value = txtBatch.Value 'batch
        .Cells(lastrow + 1, 9).Value = txtComments.Value 'comments
    End With
    
    MsgBox ("Data is added successfully")
    
End Sub

I also renamed the text boxes to make them clearer -- txtItem instead of TextBox3

[dos]

This is great. Works exactly as expected. The data is displayed in their respective columns.

I have been trying to get a new worksheet added after the last worksheet, but it is added before the first worksheet (MasterSheet). I added the command 'After', but it does not seem to work. I tried different variations to no avail. I'm just not proficient enough at VBA at the moment to work this out. I also followed instructions online, but it seems there are several ways to code this.

I also realised that after creating a new worksheet, i would have to manually add all the columns again for this new worksheet - Date/Item iD/Section/Person Responsible......etc. Is there a way to automatically create these columns when a new worksheet is created?

Thank you Paul for your great help.

Dos