Consulting

Results 1 to 5 of 5

Thread: Excel VBA UserForm not working. Need help pls!

  1. #1
    VBAX Newbie
    Joined
    Jun 2019
    Posts
    3
    Location

    Excel VBA UserForm not working. Need help pls!

    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
    Attached Files Attached Files

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,725
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Newbie
    Joined
    Jun 2019
    Posts
    3
    Location
    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

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,725
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Newbie
    Joined
    Jun 2019
    Posts
    3
    Location
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •