VBA to Concatenate three textboxes on a userform to one textbox on the same userform

[Rob Conklin]

I am working on a userform for my end users and have run into a snap. I need my user form to concatenate my Noun, Modifier, Manufacturer, Manufacturer P/N, and Extra Description textboxes into the NEW Description textbox. I have attached my spreadsheet so you can see what I am looking at. Thanks in advance for any help.

[SamT]

I did not look at your attached, but

txtNEW = txtNoun & txtModifier & txtManufacturer & txtManufacturerPN & txtExtraDescription

[mikerickson]

What code have you tried?
Something like this should work.

TextBox3.Text = TextBox1.Text & TextBox2.Text

[Rob Conklin]

Here is the code I have tried to use.

.NEW_Part_Description = CB_Noun & ";" & TB_MODIFIER & ":" & TB_Manufacturer & "," & Manufacturer_PN & "," & Extra_Description

[mdmackillop]

Private Sub NEW_Part_Description_Enter()
NEW_Part_Description.Text = CB_Noun & " " & TB_MODIFIER & " " & TB_Manufacturer _
& " " & Manufacturer_PN & " " & Extra_Description
End Sub

[Rob Conklin]

So it has to have its own sub. That is where I went wrong. I had it listed in my submit_click sub. Thanks for your help, you have been a blessing!!

[Rob Conklin]

Le me asking you another question along the same lines. Here is my submit_click code.

Private Sub Submit_Click() Dim lRw As Long

With AddExtend
lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(2, 1).Value = Me.Plant_Name.Value
.Cells(2, 2).Value = Me.Plant_Number.Value
.Cells(2, 3).Value = Me.Indicate_Action.Value
.Cells(2, 4).Value = Me.SAP_Number.Value
.Cells(2, 5).Value = Me.Purchasing_Group.Value
.Cells(2, 6).Value = Me.Profit_Center.Value
.Cells(2, 7).Value = Me.Base_Unit_of_Measure.Value
.Cells(2, 8).Value = Me.MRP_TYPE.Value
.Cells(2, 9).Value = Me.Lot_Size.Value
.Cells(2, 10).Value = Me.CB_Noun.Value
.Cells(2, 11).Value = Me.TB_Manufacturer.Value
.Cells(2, 12).Value = Me.Manufacturer_PN.Value
.Cells(2, 13).Value = Me.Extra_Description.Value
.Cells(2, 14).Value = Me.NEW_Part_Description.Value
.Cells(2, 15).Value = Me.SAP_Part_Description.Value
.Cells(2, 17).Value = Me.Minimum_Stock_Level.Value
.Cells(2, 18).Value = Me.Maximum_Stock_Level.Value
.Cells(2, 19).Value = Me.Storage_Bin_Location.Value
.Cells(2, 20).Value = Me.Material_Group.Value
.Cells(2, 22).Value = Me.Parts_Added_to_BOM.Value
.Cells(2, 23).Value = Me.Created_By.Value
.Cells(2, 25).Value = Me.Date_Created.Value
.Cells(2, 26).Value = Me.TB_Comments.Value
.Cells(2, 27).Value = Me.SAP_Vendor_Number
.Sheets("ADD-EXTEND").Range(2, 21).Value = Equip_Number_Functional_Loc_1.Value & "," & Equip_Number_Functional_Loc_2.Value & "," & Equip_Number_Functional_Loc_3 & "," & Equip_Number_Functional_Loc_4 & "," & Equip_Number_Functional_Loc_5 & "," & Equip_Number_Functional_Loc_6.Value
End With
End Sub

How would I phrase the last line of code starting with ".Sheets" to incorporate it or does that need its own sub also?

[mdmackillop]

The separate sub was just to enter the data as you tabbed through the form.

Sheets("ADD-EXTEND") is not a property of the Userform. The leading "." should be deleted.

.Sheets("ADD-EXTEND").Range(2, 21).Value =

Too much to fill in for me to test. You might consider some test code to populate the form to permit easier debugging of the code.

[Rob Conklin]

I am not sure how to do that. I am new at this. Right now, when I click my submit button on the userform, all the data in each field is pushed to the ADD-EXTEND worksheet. All I want to do it take the information that is entered into the 6 "Equip. # Functional Loc" fields on the userform and concatenate them to one cell on the ADD-EXTEND worksheet with the rest of the data.

[mdmackillop]

Should .Cells(2, 1).Value be .Cells(LRw, 1).Value etc.?

Private Sub Submit_Click()
Dim lRw As Long


With Sheets("ADD-EXTEND")
.Unprotect Password:="SPAR"  '@@@@@@@@@@@@@@@@@@
lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(2, 1).Value = Me.Plant_Name.Value
.Cells(2, 2).Value = Me.Plant_Number.Value
.Cells(2, 3).Value = Me.Indicate_Action.Value
.Cells(2, 4).Value = Me.SAP_Number.Value
.Cells(2, 5).Value = Me.Purchasing_Group.Value
.Cells(2, 6).Value = Me.Profit_Center.Value
.Cells(2, 7).Value = Me.Base_Unit_of_Measure.Value
.Cells(2, 8).Value = Me.MRP_Type.Value
.Cells(2, 9).Value = Me.Lot_Size.Value
.Cells(2, 10).Value = Me.CB_Noun.Value
.Cells(2, 11).Value = Me.TB_Manufacturer.Value
.Cells(2, 12).Value = Me.Manufacturer_PN.Value
.Cells(2, 13).Value = Me.Extra_Description.Value
.Cells(2, 14).Value = Me.NEW_Part_Description.Value
.Cells(2, 15).Value = Me.SAP_Part_Description.Value
.Cells(2, 17).Value = Me.Minimum_Stock_Level.Value
.Cells(2, 18).Value = Me.Maximum_Stock_Level.Value
.Cells(2, 19).Value = Me.Storage_Bin_Location.Value
.Cells(2, 20).Value = Me.Material_Group.Value
.Cells(2, 22).Value = Me.Parts_Added_to_BOM.Value
.Cells(2, 23).Value = Me.Created_By.Value
.Cells(2, 25).Value = Me.Date_Created.Value
.Cells(2, 26).Value = Me.TB_Comments.Value
.Cells(2, 27).Value = Me.SAP_Vendor_Number
.Cells(2, 21).Value = Me.Equip_Number_Functional_Loc_1.Value & "," _
& Me.Equip_Number_Functional_Loc_2 & "," _
& Me.Equip_Number_Functional_Loc_3 & "," _
& Me.Equip_Number_Functional_Loc_4 & "," _
& Me.Equip_Number_Functional_Loc_5 & "," _
& Me.Equip_Number_Functional_Loc_6
End With
End Sub

[SamT]

Place this code in a new Module, Named modColumnNumbers

Public Enum AddExtendColumns
  colPlantName = 1
  colPlantNum
  colIndicateAction
  colSapNum
  colPurchGroup
  colProfitCenter
  colBuom
  colMrpType
  colLotSize
  colNoun
  colManufacturer
  colMfgPartNum
  colExtraDescription
  colNewPartDescription
  colSapDescription
  colStuxureDescription
  colMinStckLvl
  colMaxStckLvl
  colBinLoc
  colMaterialGroup
  colFunctionalLoc
  colBom
  colCreatedBy
  colApprovedBy
  colDateCreated
  colComments
  colSapVendorNum
End Enum

Use this for the Submit_Click Code

Private Sub Submit_Click()
Dim FuncLoc As String
  FuncLoc = Equip_Number_Functional_Loc_1.Value & ", " _
          & Equip_Number_Functional_Loc_2.Value & ", " _
          & Equip_Number_Functional_Loc_3.Value & ", " _
          & Equip_Number_Functional_Loc_4.Value & ", " _
          & Equip_Number_Functional_Loc_5.Value & ", " _
          & Equip_Number_Functional_Loc_6.Value
  
  With ADDEXTEND.Cells(Rows.Count, 1).End(xlUp).Offset(1).EntireRow
    .Cells(colPlantName) = Me.Plant_Name
    .Cells(colPlantNum) = Me.Plant_Number
    .Cells(colIndicateAction) = Me.Indicate_Action
    .Cells(colSapNum) = Me.SAP_Number
    .Cells(colPurchGroup) = Me.Purchasing_Group
    .Cells(colProfitCenter) = Me.Profit_Center
    .Cells(colBuom) = Me.Base_Unit_of_Measure
    .Cells(colMrpType) = Me.MRP_Type
    .Cells(colLotSize) = Me.Lot_Size
    .Cells(colNoun) = Me.CB_Noun
    .Cells(colManufacturer) = Me.TB_Manufacturer
    .Cells(colMfgPartNum) = Me.Manufacturer_PN
    .Cells(colExtraDescription) = Me.Extra_Description
    .Cells(colNewPartDescription) = Me.NEW_Part_Description
    .Cells(colSapDescription) = Me.SAP_Part_Description
    .Cells(colMinStckLvl) = Me.Maximum_Stock_Level
    .Cells(colMaxStckLvl) = Me.Minimum_Stock_Level
    .Cells(colBinLoc) = Me.Storage_Bin_Location
    .Cells(colMaterialGroup) = Me.Material_Group
    .Cells(colFunctionalLoc) = FuncLoc
    .Cells(colBom) = Me.Parts_Added_to_BOM
    .Cells(colCreatedBy) = Me.Created_By
    .Cells(colDateCreated) = Me.Date_Created
    .Cells(colComments) = Me.TB_Comments
    .Cells(colSapVendorNum) = Me.SAP_Vendor_Number
  End With
End Sub

Notice how, if a control value is assigned to the wrong column, it is obvious. (Min and Max Stock levels )