Customised Part name creation from userform to worksheet

[vikrampnz]

Hello

I imagine the subject name for my thread is quite confusing, but so is trying to explain here it so I apologize in advance.

Here's what I'm trying to achieve......

I am trying to create a userform where I can enter dimensions of a cabinet i.e. Width x Depth x Height and excel can break it down into a detailed "cutlist" of the parts with respective sizes involved in making that cabinet (There are two options in this which depend on the "Cabinet Type" selection and "Back Type" selection i.e.

1) Top
2) Bottom
3) Left Side
4) Right Side
5) Back
6) Shelf

I want to be able to add such part list for multiple cabinets (that are for the same customer) in one worksheet, in order to create a comprehensive jobsheet for that customer. I have managed to achieve above, but am struggling with following parts:

1) Is there a way excel can create "customised" part names for each of the above mentioned 6 parts for individual cabinets? i.e. on my userform, I have a field named "Cabinet name - Reference". This is in order to separate one cabinet from another as they are all different sizes.

What I am hoping for is, if I enter Cabinet Name as "Cab1", then while entering the part list in worksheet the part name should look like "Cab1 - Top" or "Cab1 - Left Side" and so on.....

2) The worksheet in which I am trying to add this data has some customer details on top and hence I want the first part entry to start from row 9 onwards in the worksheet, and then following parts should just take the next emply row after the 9th row. I tried various codes from this forum, but none seems to work for me.

I have created a sample userform and worksheet, but not sure how to attach is to this thread.

Thanks in advance

 

[Tinbendr]

You'll have to use a file sharing service like Box.com or GoogleDocs.

You may have to add a SKU code column for each cabinet.

 

[vikrampnz]

Thanks David.

I have uploaded the excel file on box.com which can be found at - https://www.box.com/s/05d1dbed951d1c623f56

I am not sure what a SKU code is and how it works. My requirement is that each part in the list (for one customer) should have a unique part name as my panel saw software gets confused if there are two parts with same name and different dimensions. The part name also should be "self - explainatory" i.e. Cab1 - L. Side or Cab3 - Top, so the machine operators know where the part is going to fit and drill holes accordingly.

These part names have to be unique only for that specific customer. For the next customer, we can again have a part named "cab1 - L. Side" or "Cab3 - Top".

At the moment, I have managed to use formulae in excel to calculate the required part sizes and then display them on the userform via labels. There are 2 comboboxes on the userform with 2 options each. These combobox selections define the output displayed on userform.

 

[Tinbendr]

Move userform data to customer sheet

Drop this in the userform code page and see if it'll get you started.

You'll need to add a textbox for a series number. (Or dropdown) As it is now, I've hard coded SR1.

Since the userform already posts the calculated data back to the worksheet, I pull that data to the customer page.

Private Sub CommandButton2_Click()
Dim WS As Worksheet
Dim MD As Worksheet
Dim NextRow As Long
Dim A As Long
Dim B As Integer

    Set WS = ActiveWorkbook.Worksheets("Customer Estimate")
    Set MD = ActiveWorkbook.Worksheets("MasterData")

    With WS
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

        For A = 0 To 5
            'Series Number
            .Cells(NextRow + A, "A").Value = "SR1"

            'Part Name
            If A = 5 Then
                B = UserForm1.ComboBox2.ListIndex
                .Cells(NextRow + A, "B").Value = Me.TextBox7.Value & "-" & MD.Range("A" & 23 + A + B)
            Else
                .Cells(NextRow + A, "B").Value = Me.TextBox7.Value & "-" & MD.Range("A" & 23 + A)
            End If
            'Length
            .Cells(NextRow + A, "C").Value = MD.Range("B" & 23 + A)

            'Width
            .Cells(NextRow + A, "D").Value = MD.Range("C" & 23 + A)

            'Qty
            .Cells(NextRow + A, "E").Value = Me.TextBox5.Value
        Next
    End With
End Sub

 

[vikrampnz]

Thanks a lot David! It worked brilliantly well.....did exactly what I wanted!

I need one more small favour and I would really appreciate if you can help...

There is a textbox on the userform for "no. of shelves". At the moment, the current code is defaulting to 1 Shelf for every cabinet. I want to be able to define no of shelves for each cabinet as it varies from cabinet to cabinet. For some cabinets, that number is 0 and for others it can be anything between 0 and 10. Could you please guide me on how I can achieve this?

Everything else is just perfect!

Thanks again for your help!

Cheers
Vikram

 

[Tinbendr]

I had to break it out a little. It duplicates some code, but it's easy to read this way.

Replace the previous code with this.

Private Sub CommandButton2_Click()
Dim WS As Worksheet
Dim MD As Worksheet
Dim NextRow As Long
Dim A As Long
Dim B As Integer

    Set WS = ActiveWorkbook.Worksheets("Customer Estimate")
    Set MD = ActiveWorkbook.Worksheets("MasterData")

    With WS
        'Last row in Column A + 1.
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        'Top, Bottom, Sides
        For A = 0 To 3
            'Series Number
            .Cells(NextRow + A, "A").Value = "SR1"

            'Part Name
            .Cells(NextRow + A, "B").Value = Me.TextBox7.Value & "-" & MD.Range("A" & 23 + A)

            'Length
            .Cells(NextRow + A, "C").Value = MD.Range("B" & 23 + A)

            'Width
            .Cells(NextRow + A, "D").Value = MD.Range("C" & 23 + A)

            'Qty
            .Cells(NextRow + A, "E").Value = Me.TextBox5.Value
        Next

        'Back Flush/Grooved? Determined by combobox dropdown.
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        B = UserForm1.ComboBox2.ListIndex
        'Series Number
        .Cells(NextRow, "A").Value = "SR1"

        'Part Name
        .Cells(NextRow, "B").Value = Me.TextBox7.Value & "-" & MD.Range("A" & 28 + B)

        'Length
        .Cells(NextRow, "C").Value = MD.Range("B" & 28 + B)

        'Width
        .Cells(NextRow, "D").Value = MD.Range("C" & 28 + B)

        'Qty
        .Cells(NextRow, "E").Value = Me.TextBox5.Value

        'Shelf
        If Me.TextBox4.Value > 0 Then
            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            'Series Number
            .Cells(NextRow, "A").Value = "SR1"

            'Part Name
            .Cells(NextRow, "B").Value = Me.TextBox7.Value & "-" & MD.Range("A27")

            'Length
            .Cells(NextRow, "C").Value = MD.Range("B27")

            'Width
            .Cells(NextRow, "D").Value = MD.Range("C27")

            'Qty
            .Cells(NextRow, "E").Value = Me.TextBox4.Value * Me.TextBox5.Value
        End If
    End With
End Sub

 

[vikrampnz]

Re: Move userform data to customer sheet

Thanks a lot David. It worked just fine!

While checking the previous code, I found a small problem.

On the userform, there is a combobox (combobox1) for defining the "Cabinet Type". Depending on the cabinet type selected in the combobox, it changes the dimensions shown on the userform for each part. In the masterdata worksheet, I have made two separate calculations for the two cabinet types i.e. 1) Full Top 2) Full Sides and depending on the option selected in combobox1, it shows relevant dimensions on userform labels for part sizes.

When I click on the "Add to Estimate" button, it only transfers data for "Full Top" option to "Customer Estimate" worksheet irrespective of the option selected on userform.

Could you please suggest how I can eliminate this problem? I have 2 separate part size calculations for !) Full Top & 2) Full Sides in the "masterdata" worksheet.

Thanks
V

 

[vikrampnz]

Re: Move userform data to customer sheet

Is it possible to capture values shown on the userform labels and transfer them to the worksheet instead of using the ones from excel? As after doing the selection from the combobox, the userform already displays the correct values.....just wondering if we can transfer them from there....thanks