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.
I did not look at your attached, butCode:txtNEW = txtNoun & txtModifier & txtManufacturer & txtManufacturerPN & txtExtraDescription
What code have you tried?
Something like this should work.
Code:TextBox3.Text = TextBox1.Text & TextBox2.Text
Here is the code I have tried to use.
Quote:
.NEW_Part_Description = CB_Noun & ";" & TB_MODIFIER & ":" & TB_Manufacturer & "," & Manufacturer_PN & "," & Extra_Description
Code:Private Sub NEW_Part_Description_Enter()
NEW_Part_Description.Text = CB_Noun & " " & TB_MODIFIER & " " & TB_Manufacturer _
& " " & Manufacturer_PN & " " & Extra_Description
End Sub
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!!
Le me asking you another question along the same lines. Here is my submit_click code.
How would I phrase the last line of code starting with ".Sheets" to incorporate it or does that need its own sub also?Quote:
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
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.
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.Code:.Sheets("ADD-EXTEND").Range(2, 21).Value =
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.
Should .Cells(2, 1).Value be .Cells(LRw, 1).Value etc.?
Code: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
Place this code in a new Module, Named modColumnNumbersUse this for the Submit_Click CodeCode: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
Notice how, if a control value is assigned to the wrong column, it is obvious. (Min and Max Stock levels :devil2: )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