Sales worksheet with an if then query

[ironwood40]

Here is my question...I have a workbook that my sales reps use to input orders...we sell a variety of promotional products. What I need is when the sales rep is placing an order for apparel, on the cost sheet where they put all of their information, which then populates the estimate and P.O. tabs, I want to add a column before the count column that is titled “Apparel”. If they put a Y in any cell below that title, it will automatically take them to another worksheet in the workbook to input the following: item number, description, color, size and lastly quantity. When they are done inputting the info on that worksheet, they will go back to the cost worksheet and all the info has been sorted and counted but added as a text line under description, not on separate lines but as a summary by item number and color. As I mentioned, that info populates the estimate tab for the client and the P.O. tab for the vendor....

is this possible...I am a bit above novice excel user but programmer I am not...thanks for any help you all can give me...

 

[Dim Me as xlNoob]

I feel like this would be fairly easily executed with a userform. A Worksheet_Change event can open the userform whenever you change a cell in the "Apparel" column to "Y". You can put in as many userform controls as you want to be filled out and once they close the userform all the values can be sent where they need to go as well as concatenated into a single string to be placed in your product description cell.

I'd be happy to help with the macro/userform side of things but would need more info on where all the data needs to go etc.

 

[ironwood40]

I would love your help...I can send you the workbook with the new tab included and show examples of what I need if that’s possible...

 

[Dim Me as xlNoob]

Ok I've tried to do a mock-up so I can paste the code here and you can implement it in your spreadsheet. The table below is where your Apparel column would be and when a "Y" is placed in it it opens a userform and asks dor all the details then populates the Description column associated with the Apparel column.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Heading 1[/TD]
[TD="align: center"]Heading 2[/TD]
[TD="align: center"]Apparel[/TD]
[TD="align: center"]Count[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Will go here[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So in your sheet module where the above table is:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    
    Application.EnableEvents = False
    
    If Target.Cells.Count > 1 Then
        Application.EnableEvents = True
        Exit Sub
    End If
    
    If Target.Value = "y" Then Target.Value = "Y"
    
    Set rTarget = Target.Offset(0, 2)
    Set rng = Range("ApparelRange")
    
    If Not Intersect(Target, rng) Is Nothing And Target.Value = "Y" Then
        ItmDetails.Show
    End If
    
    Application.EnableEvents = True
End Sub

Then I created a userform with a few textboxes asking for all the details you listed and in the code in the userform looks like:

Private Sub Cancel_Click()
    Unload Me
End Sub
Private Sub Submit_Click()
    Dim Description As String, DblLine As String
    Dim ctrl As MSForms.Control, emptyCtrl As Boolean, continue As Boolean
    
    emptyCtrl = False
    
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            If ctrl.Value = "" Then emptyCtrl = True
        End If
    Next ctrl
    
    If emptyCtrl = True Then
        continue = MsgBox("There is an empty field, do you want ot continue?", vbYesNo)
        If continue = False Then
            Application.EnableEvents = True
            Exit Sub
        End If
    End If
    
    ' Here you would send the userform values to any destinations they need to go.
    
    DblLine = Chr(10) & Chr(10)
    
    Description = "Item Number: " & ItmNo.Value & DblLine & "Description: " & ItmD.Value & DblLine & "Color: " & ItmColor _
                  & DblLine & "Size: " & ItmSize & DblLine & "Quantity: " & ItmQty
    
    rTarget.Value = Description
    
    Set rTarget = Nothing


    Unload Me
End Sub

The ItmNo, ItmD, ItmColor, ItmSize, and ItmQty are the names of the textboxes where all the details are input by the user. This code asks if you want to continue if not all fields are entered. You can change it to not allow them to continue without filling all required fields.

Lastly but very important you need to create a new module and place this line in it:

Public rTarget As Range

That is a public variable which makes it possible to reference which cell you changed outside of the change event.

Have a go at implementing this into your spreadsheet. Let us know how it goes.

Good luck!

 

[Macropod]

I can send you the workbook

Please don't try to engage others in off-line discussion; doing so is a violation of Rule 4: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
If you need to show use what your workbook looks like, use one of the Posting Aids mentioned in the Forum Guidelines: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html