Userform to add, chage status and show all available products.


New Member
Jul 18, 2021
Office Version
  1. 2016
  1. Windows
Hello ladies & gentelmans,
I was wondering if someone could help me with this, a new problem has arisen,i have one workbook that i use to store informations about products in my inventroy and track stock status.
Right now my workbook have 33 worksheets for each product one sheet (List of sheet names in sampleworkbook), all worksheets have 7 columns ("A:G") and X rows. I update manually data, on daily basis and its soo much time consuming. I want to upgrade that workbook to be more automative, but due to my low knowledge of VBA i stuck after few steps from begining... I will put here link of sample workbook. I created a UserForm which should populate required columns with data from TextBoxes. In first section of userform i need to add data, i found some code here on forum to enter data to selected worksheet in ComboBox, and it works almost as expected, i need when entering serial number of product example: serial 6000514 and first number of box is lets say 1 and last is 5, i also found code here for this but it needs to be modified to generate something like this in column "A" rows "1-5" 6000514-1,6000514-2,6000514-3,6000514-4,6000514-5.
Other columns should be filled with same text in each row from Textboxes.
Sample workbook with codes and some exampe worksheets in this link:
Screen shot of UserForm and how it should add data: sample-WS Screenshot of Sheet 1 how should serial number look. samplews1 Code for this is below.
In second section of userform i need to be able to find product in range by serial number and chage its status in column "E" from "On Stock" to "Sent" for example: All products in range 6000514-1-3 change to "Sent". I can t figure this out how to make it on my own...
And finaly if its possible to make a preview list of all products that are "On Stock" in separete sheet ("Blanko List") after i click comand button so that i can print that data. Later that list can be deleted, i just need to view and print Many thanks in advance, any help is welcome.

Code i found and edit to add data:
VBA Code:
Private Sub CommandButton2_Click()
TargetSheet = ComboBox1.Value
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 2).Value = ComboBox1.Value
ActiveSheet.Cells(lastrow + 1, 1).Value = TextBox1.Value
ActiveSheet.Cells(lastrow + 1, 3).Value = TextBox3.Value
ActiveSheet.Cells(lastrow + 1, 4).Value = TextBox4.Value
ActiveSheet.Cells(lastrow + 1, 5).Value = ComboBox2.Value
ActiveSheet.Cells(lastrow + 1, 6).Value = TextBox6.Value
End Sub

Code i found for my Serial number, code is located in Sheet1, just for testing.
VBA Code:
Sub expandValues()
    Dim i As Long, j As Long, arr As Variant

    With Worksheets("sheet1")
        .Cells(1, "E").Resize(1, 2) = Array("Serial Number", "Value")

        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            ReDim arr(.Cells(i, "B").Value2 To .Cells(i, "C").Value2, 1 To 2)
            For j = LBound(arr, 1) To UBound(arr, 1)
                arr(j, 1) = .Cells(i, "A").Value2
                arr(j, 2) = j
            Next j
            .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0). _
              Resize(UBound(arr, 1) - LBound(arr, 1) + 1, UBound(arr, 2)) = arr
       Next i
End With
End Sub
Last edited by a moderator:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
your attached workbook does not include your codes or userform?

Upvote 0

Forum statistics

Latest member
edris Alsatouf

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