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

[mir994stan]

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:

Download Sample WB.xlsm (38.75 KB) now. Fast and easy at workupload.com

Download Sample WB.xlsm (38.75 KB) now. Fast and easy at workupload.com

workupload.com

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:

Private Sub CommandButton2_Click()
TargetSheet = ComboBox1.Value
Worksheets(TargetSheet).Activate
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.

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

 

[dmt32]

Hi,
your attached workbook does not include your codes or userform?

Dave

 

[mir994stan]

Hi Dave,
Thank you for noticing, somehow its missing. Here is the correction on this link: Download Sample WB.xlsm (77.75 KB) now. Fast and easy at workupload.com
With help of the community i menage to complete 2 of 3 problems, now is left status change to deal with, all is expalined in this thread : Change all values in column E between two rows?

Mirko