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 :devil2: )