Userform to Add, Search Record in Multiple Sheet

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Hi, I am very new to excel userform. I have a workbook with 9 sheets (9 different category, but with same fields).

CategoryProduct IDProduct NameStock
BB0001product 120
BB0002product 210

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...

VBA Code:
    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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

Rich (BB code):
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

Rich (BB code):
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
 
Upvote 0
Thank you sir for your help. I will try to fix it based on the code that you gave me. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top