Userform to Add, Search Record in Multiple Sheet

[paddybear]

Hi, I am very new to excel userform. I have a workbook with 9 sheets (9 different category, but with same fields).

Category	Product ID	Product Name	Stock
B	B0001	product 1	20
B	B0002	product 2	10

A simple userform
- txtCategory (combo box)
- txtPID
- txtPName
- txtStock

My questions are:-
1) How can i write the vba code to ADD record into diff sheet based on selected Category?
2) How to search the last ProductID based on selected Category? (I plan to do the autorun number)

My current code as below...

    Dim lRow As Long
    Dim wsB As Worksheet
    Dim wsJ As Worksheet
    Dim wsF As Worksheet
    Dim wsS As Worksheet
    Dim wsA As Worksheet
    Dim wsZ As Worksheet
    Dim wsSG As Worksheet
    Dim wsHK As Worksheet
    Dim wsR As Worksheet
        
    Dim myCat As String
    
    myCat = txtCategory.Text
    
    If myCat = "J" Then
        Set wsJ = Worksheets("J")
        lRow = wsJ.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    With wsJ
        .Cells(lRow, 1).Value = txtCategory.Text
        .Cells(lRow, 2).Value = txtCategory.Text
        .Cells(lRow, 3).Value = StrConv(txtID.Text, vbProperCase)
        .Cells(lRow, 4).Value = txtStock.Value
        .Cells(lRow, 5).Value = txtPrice.Value
        .Cells(lRow, 6).Value = txtType1.Text
        .Cells(lRow, 7).Value = txtType2.Text
        
        MsgBox StrConv(txtID.Text, vbProperCase), vbOKOnly, "Added"
        sClear
    End With
    
    End If
    
    
    If myCat = "A" Then
        Set wsA = Worksheets("A")
        lRow = wsA.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    With wsA
        .Cells(lRow, 1).Value = txtCategory.Text
        .Cells(lRow, 2).Value = txtCategory.Text
        .Cells(lRow, 3).Value = StrConv(txtID.Text, vbProperCase)
        .Cells(lRow, 4).Value = txtStock.Value
        .Cells(lRow, 5).Value = txtPrice.Value
        .Cells(lRow, 6).Value = txtType1.Text
        .Cells(lRow, 7).Value = txtType2.Text
        
        MsgBox StrConv(txtID.Text, vbProperCase), vbOKOnly, "Added"
        sClear
    End With
    
    End If

 

[dmt32]

Hi welcome to forum

To answer first part of your question, If your combobox contains all the sheet names you want your code to post the data to, then you just place your variable MyCat in the part of the code where you set your ws object variable like this

Set ws = ThisWorkbook.Worksheets(myCat)

And you only need to do this once, so your full code for ALL your worksheets would look like this

Private Sub CommandButton1_Click()
    Dim lRow As Long
    Dim ws As Worksheet
    Dim myCat As String
    
    myCat = txtCategory.Text
    If Len(myCat) = 0 Then Exit Sub

    Set ws = ThisWorkbook.Worksheets(myCat)
    
    With ws
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        .Cells(lRow, 1).Value = txtCategory.Text
        .Cells(lRow, 2).Value = txtCategory.Text
        .Cells(lRow, 3).Value = StrConv(txtID.Text, vbProperCase)
        .Cells(lRow, 4).Value = txtStock.Value
        .Cells(lRow, 5).Value = txtPrice.Value
        .Cells(lRow, 6).Value = txtType1.Text
        .Cells(lRow, 7).Value = txtType2.Text
     End With
     
    MsgBox StrConv(txtID.Text, vbProperCase), vbOKOnly, "Added"

End Sub

Note that if control txtCategory is a combobox, naming convention for such a control is normally cboCategory.

Its not mandatory but it helps if controls are named in a consistent manner to identify object type.

Try update & see if helps you & I or others here should be able to assist with next part.


Dave

 

[paddybear]

Thank you sir for your help. I will try to fix it based on the code that you gave me.